CODEWITHSUNDEEP
mysqlphpmyadmin
Ish
Ish

Reputation: 21

How to auto increment for varchar in MYSQL

It is possible to increment numbers automatically in MySQL. Is there any way to increment for varchar in MYSQL, example "SL00001" it will increment to "SL00002"?

Upvotes: 1

Views: 886

Answers (1)

Akina
Akina

Reputation: 42632

You can do it automatically using MyISAM engine and appliing Nick's comment "Just use a numeric id value and SELECT CONCAT('SL', LPAD(id, 5, '0')) AS id":

CREATE TABLE test (prefix CHAR(2),
                   num INT NOT NULL AUTO_INCREMENT,
                   PRIMARY KEY (prefix, num),
                   val INT) ENGINE = MyISAM;

INSERT INTO test (prefix, val) VALUES
('AX',11),
('AX',22),
('LZ',33),
('AX',44),
('LZ',55),
('LZ',66);

SELECT *, CONCAT(prefix, LPAD(num, 5, '0')) AS id 
FROM test
ORDER BY id;

prefix | num | val | id     
:----- | --: | --: | :------
AX     |   1 |  11 | AX00001
AX     |   2 |  22 | AX00002
AX     |   3 |  44 | AX00003
LZ     |   1 |  33 | LZ00001
LZ     |   2 |  55 | LZ00002
LZ     |   3 |  66 | LZ00003

db<>fiddle here

Upvotes: 3

Related Questions