Is there a way to insert an auto-incremental primary id with a prefix in mysql database?

Question

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?

If the "D" is always the same then it does not need to be part of the field. Use a standard auto increment integer and add the "D" in a view. — Declan_K, Aug 23 '13 at 14:55
is it possible to break it up into two seperate fields and then concat them together when needed? — amaster, Aug 23 '13 at 14:55
This is a wild guess, but your next question will be How do I sort these in order so D2 comes out before D10 — Tony Hopkinson, Aug 23 '13 at 14:57
"add the D in a view"-- I don't understand how to do it. Can you explain it please? @Declan_K — Tahsin Abrar, Aug 23 '13 at 14:59
have a look here: http://stackoverflow.com/a/6630656/1121982 It's useful — Vahid Hallaji, Aug 23 '13 at 15:01
Yeah.. I forget about it.. So, if I want to insert D02 instead of D2, how can I do it? @TonyHopkinson — Tahsin Abrar, Aug 23 '13 at 15:02
As other's have said. Don't! As soon as you try to go down this route everything becomes harder. However the function you want is LPad. — Tony Hopkinson, Aug 23 '13 at 15:16

peterm · Accepted Answer · 2013-08-23T15:35:37.143

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 SQLFiddle demo

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 SQLFiddle demo

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

@Akash It's up to OP to follow business requirements and decide what value length is appropriate. — peterm, Aug 23 '13 at 15:26
@Jack If you mean after insert into table1 then no it obviously won't. — peterm, Aug 23 '13 at 15:48
Thanks a lot @peterm I solved it with your first procedure. Thanks again :D — Tahsin Abrar, Aug 23 '13 at 18:28

score 0 · Answer 2 · edited Aug 23 '13 at 15:33

0

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.

edited Aug 23 '13 at 15:33

Bridge

29,818
9
60
82

answered Aug 23 '13 at 15:20

Kanagaraj M

956
8
18

Is there a way to insert an auto-incremental primary id with a prefix in mysql database?

2 Answers2

Linked