Is There A Way To Insert An Auto-incremental Primary Id With A Prefix In Mysql Database?

I'm trying to insert a data as a primary ID that has one alphanumerical value and two numerical value in MySQL database. This data will auto incrementally generate number, but the

Solution 1:

First of all it's unadvisable to do so, like others commented, you can have this id value generated on the fly.

But if nonetheless you want it your way there're at least two ways to do so:

More or less reliable way involves using a separate table for sequencing and a trigger

Schema:

CREATETABLE Table1_seq 
(
  id INTNOTNULL AUTO_INCREMENT PRIMARY KEY
);
CREATETABLE Table1
(
  `id` VARCHAR(10) NOTNULLPRIMARY KEY DEFAULT'',
   ...
);

Trigger:

DELIMITER $$
CREATETRIGGER tg_bi_table1
BEFORE INSERTON table1
FOREACHROWBEGININSERTINTO table1_seq() VALUES();
  SET NEW.id = CONCAT('D', LPAD(LAST_INSERT_ID(), 4,'0'));
END$$
DELIMITER ;

Then you just insert your rows to table1

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

And you'll get

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

Here is SQLFiddle demo

Unreliable way is to generate your new id on the fly in INSERT statement itself

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

Here is SQLFiddle demo

The problems with this approach:

1. Under heavy load two concurrent sessions can grab the same MAX(id) value and therefore generate the same new id leading to the failure of insert.
2. You can't use multi-insert statements

Solution 2:

We can't set auto-increment for alphanumeric. In your case if D is always same then no need to add it to your pk field. Keep your constant in a separate field and add it when you select.