-- option 1 Create table badge( id INT AUTO_INCREMENT, name VARCHAR (20) NOT NULL, code CHAR (3) AS (SUBSTRING(name,1,2)), -- concat on id auto increment does not work PRIMARY KEY (ID) ) ;

-- option 2 Create table badge1( id INT, name VARCHAR(20) NOT NULL, code CHAR(3) AS (concat(SUBSTRING(name,1,2),id)), PRIMARY KEY (ID) )

insert into badge (name) values ('myname')

insert into badge1 (id,name) values (1,'myname')

select id,name,concat(code,id) code from badge

select id,name,code from badge1