Question 113570 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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
$$
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search