CODEWITHSUNDEEP
mysqlauto-increment
sebastiano
sebastiano

Reputation: 31

How to give a special format to a number with auto_increment in MYSQL?

I want to give a specific format to an users's id in MYSQL, but it must be auto_increment type. My idea is to do this: US01, US02, US03,US04...

I don't know how to do this type of ID, I'd greatly appreciate your help

Upvotes: 0

Views: 3569

Answers (1)

O. Jones
O. Jones

Reputation: 108806

Autoincrementing numbers in MySQL have either type INT or type BIGINT. You can't assign another data type to an autoincrementer. (So, it's not surprising you don't know how to do it, eh?)

You could, if you were working in MariaDB or in MySQL 5.7.5+, create a virtual (computed) column transforming your integer autoincrementer into the format you want. If your autoincrementing column were user_id, this would give you a virtual column with the id value you want.

  ALTER TABLE mytable 
   ADD COLUMN useridtext varchar(10) 
              GENERATED ALWAYS AS 
                (CONCAT('US', LPAD(user_id,8,'0')))
              PERSISTENT

makes a new virtual column, in MariaDB, named useridtext. It contains values like US00000001, US00000002, and so forth.

The same could be done in any version of MySQL by creating a view of your table.

Upvotes: 2

Related Questions