Having an incremented ID with string value

Question

I need some help, I want some string ID that can be incremented! how will i do that?

This is the old ones:

    Product_ID        Product_Name
    1                 Milk
    2                 Beer
    3                 Hot Chocolate

I want it to be like this:

    Product_ID        Product_Name
    PROD-0001         Milk
    PROD-0002         Beer
    PROD-0003         Hot Chocolate

How will I be able to make the Product_ID like that? thank you so much for the answers

You have to use a trigger if you want to store the value in the table. I would suggest you re-think your design and convince yourself of the benefits of an auto-incremented/identity/serial numeric id. — Gordon Linoff, Sep 02 '16 at 11:02
Possible duplicate of [How to make MySQL table primary key auto increment with some prefix](http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix) — Vishnu Bhadoriya, Sep 02 '16 at 11:02
One way is to create loop and check with query if any productID is existed. If so, then use a counter to add the products with new productID and string concatenation. You can't auto-increment a string. — AT-2017, Sep 02 '16 at 12:32

score 0 · Answer 1 · answered Sep 02 '16 at 11:47

0

MySQL 5.7.6 added generated columns. You could use that to produce a modified product ID:

ALTER TABLE YourTable
    ADD Product_ID_String AS CONCAT('PROD-', LPAD(Product_ID, 4, '0'));

answered Sep 02 '16 at 11:47

Andomar

1 Answers1