Auto create unique ID based on id-CodePudding

Based on my question above, I have a MySQL table called qrc_creation. This table consists of columns like id (auto increment), creation_code, and creation_name. For example, if I want to insert a new creation_name, the ID will auto 1. But, I also want the creation_code to become qrc_00000001, where 1 comes from ID.

Thus, can I know what is the query to do this? Thank you in advance!

CodePudding user response：

You have two options. If you want, the column to autopopulate during insert, you can use MySQL generated columns while defining table schema. However, you cannot use Auto Increment column with this method.

CREATE TABLE `table_1` (
  `id` INT(10) ZEROFILL NOT NULL,
  `creation_name` VARCHAR(45) NOT NULL,
  `creation_code` VARCHAR(55) GENERATED ALWAYS AS (CONCAT(`name`, '_', `id`)),
  PRIMARY KEY (`id`));

If you don't want that dedicated column in your table, you can easily get calculated field on your SQL query by using a simple concat function.

SELECT 
    `id`, `creation_name`, CONCAT(`name`, '_', `id`) AS `creation_code`
FROM
    table_1;

Hope it helps.

CodePudding user response：

first of all you need to show your code so that stackoverflow community respond. But still i got your problem and given below is the solution-

CREATE TABLE qrc_creation
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   creation_code AS 'qrc'   RIGHT('0000000'   CAST(ID AS VARCHAR(7)), 7) PERSISTED,
   creation_name varchar(255),
  );

  Select * from qrc_creation;
 INSERT INTO qrc_creation(creation_name)
VALUES ('Monsen');
  Select * from qrc_creation;

Hope you like my answer.