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

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.

CodePudding user response：

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;

CodePudding user response：

You could do it in the database via a trigger. What you would need to do is to use an auto_increment-column and get the value of it in the BEFORE-trigger:

delimiter $$
drop table thetable
$$
create table thetable (
id int auto_increment,
id_text varchar(20),
another varchar(20),
primary key(id),
unique index(id_text)
)
$$

CREATE TRIGGER thetable_ibefore
BEFORE INSERT ON thetable
FOR EACH ROW  
BEGIN  

DECLARE v_id int;

select auto_increment into v_id
from information_schema.tables
where table_schema = database() and table_name = 'thetable';

SET NEW.id_text = concat('UID',substr(concat('000000', v_id), -7)); 
END;
$$

insert into thetable (another) values ('ABC')
$$

select * from thetable
$$