Search code examples
mysqluid

How to create a custom id using auto increment at the time of table creation in My SQL

Is there a way to set at the time of creating a table a custom ID with some character as prefix and the rest are numbers which is auto incremented so that the first time a record is inserted the ID will be "UID0000001" and the second time a record is inserted the ID will be "UID0000002" and so on automatically in MySQL.

Solution

  • Assuming the prefix would always be UID, then you should just maintain an auto increment id column and then build the UIDxxxxx value when you query or in your presentation layer. If the prefix could vary, then you would need to state the rules if you want a concrete answer.

    For example:

    CREATE TABLE yourTable (
    id int NOT NULL AUTO_INCREMENT,
    some_col varchar(255) NOT NULL,
    -- other columns here
    PRIMARY KEY (id)
);

SELECT CONCAT('UID', LPAD(id, 7, '0')) AS uid
FROM yourTable;