1

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"?

Ish
  • 21
  • 5
  • question. is SL00001 the latest record? or are we doing the row_number() function on it? – arcee123 Feb 03 '20 at 05:22
  • 4
    Just use a numeric `id` value and `SELECT CONCAT('SL', LPAD(id, 5, '0')) AS id` – Nick Feb 03 '20 at 05:22
  • maybe able to use [generated column](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) on the numeric `id` to transform it and make it look like a real column. Auto_increment columns are a integer type only. – danblack Feb 03 '20 at 05:58
  • @danblack Generated column cannot refer to autoincremented field. – Akina Feb 03 '20 at 06:01
  • ack, had a feeling that may be a constraint. Thanks. – danblack Feb 03 '20 at 06:01
  • 1
    You can use composite primary key for this purpose after adding 2 columns, one for integer and other for varchar. This way you can get a clear user view as well as can reduce code cumbersome in your back-end code as well. – ShaL Feb 03 '20 at 09:20
  • this might help you. [MySql AutoIncrement ](https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix) – César Massruha Feb 07 '22 at 01:23

1 Answers1

3

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

Akina
  • 39,301
  • 5
  • 14
  • 25