How to create a unique autoincrement field with a string prefix?

Question

I have a table 'user' with 3 items.

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`customernumber` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`type` enum('customer','admin') COLLATE utf8_unicode_ci NOT NULL,

How can I increment 'customernumber' with the first two letters of the 'type'?

For example, user with ID=10 and type='admin' will have a customernumber 'AD000010'. And user with ID = 12 and type = 'customer' will have a customernumber 'CU000012'?

Is this possible to do this in MySQL without using a trigger? If not, how can I do this with a trigger?

Why do you need to store this in a single column? They're two discrete pieces of data. Store them in separate columns and concatenate them when you extract them from the database, if you require that. — Ben
– Ben, Commented Nov 30, 2013 at 13:29

Community · Accepted Answer · 2017-05-23 11:59:26Z

4

What you could do is store the key as two columns. A char prefix and an auto-incrementing int, both of which are grouped for the primary key.

CREATE TABLE myItems (
    id INT NOT NULL AUTO_INCREMENT,
    prefix CHAR(30) NOT NULL,
    PRIMARY KEY (id, prefix),
    ...

Please refer to this link How to make MySQL table primary key auto increment with some prefix hope this help you .

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Nov 30, 2013 at 13:43

user3014966

2631 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a unique autoincrement field with a string prefix?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related