mysql 自增长 前缀,有没有办法在mysql数据库中插入带有前缀的自动增量主id？

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 alphanumerical value will be fixed. Like, D1, D2....D54, D55, D56 etc. Here, 'D' is always the same, but the number will be automatically incremented. Is there any way to do this?

解决方案

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:

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 to table1

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

And you'll get

| ID |

---------

| D0001 |

| D0002 |

| D0003 |

Here is

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

INSERT INTO Table1 (id, ...)

SELECT CONCAT('D', LPAD(COALESCE(SUBSTR(MAX(id), 2), 0) + 1, 4, '0')),

...

FROM table1

Here is

The problems with this approach:

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.

You can't use multi-insert statements