How to make MySQL table primary key auto increment with some variable prefix

Question

How to make MySQL table primary key auto increment with some variable prefix ??

I am trying to make auto increment field using trigger in which both string+number are variable.For example PRJ001, PRJ002, PRD001, PRD002...etc. How can I implement this either using trigger or using simple query ???

Any suggestion to make my day?

Store the string and number separately and don't use auto_increment. Despite its name, that's not what it's for. — Strawberry, Aug 28 '15 at 07:09
Can this be done, sure. I would do it thru a stored proc, locking, spend 20 minutes, then wonder why it really mattered — Drew, Aug 28 '15 at 07:30
Here is that [stored proc](http://stackoverflow.com/a/32018207/1816093) I was referring to. It means your inserts happen thru a stored proc, not an insert statement natively. That issue over there was about how insert on duplicate update actually creates gaps in the auto_increment value. The stored proc could be tailored for your purposes. But don't. The concept is insane. Don't use it, tailor it, it will cause unnecessary insanity. — Drew, Aug 28 '15 at 07:44

Your Common Sense · Answer 1 · 2015-08-28T07:22:51.507

2

Like it is said in the comments, you can't use autoincrement for this.

In your place I wouldn't use separate enumeration, but rather one for all: PRJ001, PRJ002, PRD003, PRD004, PRJ005 and so on.

After all it's just a name, without any significance in the number.

edited Aug 28 '15 at 07:22

answered Aug 28 '15 at 07:09

Your Common Sense

156,878
40
214
345

but after some insertion of 'PRJ' i want 'PRD' as a prefix. And there is also chance that after some 'PRD' insertion i want to insert with 'PRJ'. – Trilok Patel Aug 28 '15 at 07:12
And In this Condition is that Once new prefix are come you have to start your counter with 0 – Trilok Patel Aug 28 '15 at 07:13
I agree with YCS. Don't get all OCD over your id numbers – Drew Aug 28 '15 at 07:26

score 2 · Answer 2 · answered Aug 28 '15 at 07:09

I do not think thats possible, The are two ways I know on how you could achieve this:

Use INT field and translate it to whatever you want when you read it.
make it a var charand insert it manually (do calculation on your code before adding to the database)

How to make MySQL table primary key auto increment with some variable prefix

2 Answers2