MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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);

Records: 6  Duplicates: 0  Warnings: 0


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

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