Is there a way to insert an auto-incrementing primary identifier with a prefix in the mysql database?

Question

I am trying to insert data as a primary identifier that has one alphanumeric value and two numeric values ​​in a MySQL database. This data will automatically increase the numerical number, but the alphanumeric value will be corrected. For example, D1, D2 .... D54, D55, D56, etc. Here "D" is always the same, but the number will automatically increase. Is there any way to do this?

Answer 1

First of all, it is impractical to do this , like other comments, you can get this id value generated on the fly.

But if you still want your path to be in at least two ways:

A more or less reliable method involves using a separate table for the sequence and trigger

Scheme:

CREATE TABLE Table1_seq ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE Table1 ( `id` VARCHAR(10) NOT NULL PRIMARY KEY DEFAULT '', ... ); 

Trigger:

 DELIMITER $$ CREATE TRIGGER tg_bi_table1 BEFORE INSERT ON table1 FOR EACH ROW BEGIN INSERT INTO table1_seq() VALUES(); SET NEW.id = CONCAT('D', LPAD(LAST_INSERT_ID(), 4,'0')); END$$ DELIMITER ; 

Then you just insert your rows in table1

 INSERT INTO Table1 () VALUES (),(),(); 

And you will get

  |  ID |
 ---------
 |  D0001 |
 |  D0002 |
 |  D0003 |

Here is the SQLFiddle demo

An unreliable way is to generate a new identifier on the fly in the INSERT

 INSERT INTO Table1 (id, ...) SELECT CONCAT('D', LPAD(COALESCE(SUBSTR(MAX(id), 2), 0) + 1, 4, '0')), ... FROM table1 

Here is the SQLFiddle demo

Problems with this approach:

• Under heavy load, two concurrent sessions can capture the same MAX(id) value and, therefore, create the same new identifier, which will lead to insert failure.
• You cannot use multi-insert statements

Answer 2

We cannot set auto-increment for alphanumeric. In your case, if D is always the same, then you do not need to add it to your pk field. Keep your constant in a separate field and add it when you select.