CODEWITHSUNDEEP
mysqlauto-increment
user3034740
user3034740

Reputation: 121

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

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?

Upvotes: 0

Views: 7432

Answers (1)

user3014966
user3014966

Reputation: 263

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 .

Upvotes: 4

Related Questions