Mariadb auto_increment different series for another column

Question

I'd like to create a table something like.

CREATE TABLE `testing` (
  `prefix` tinyint(3) unsigned NOT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`,`prefix`) USING BTREE
);

and with an insert statement like

insert into testing(prefix) values(1),(1),(2),(2);

I'd like to get

prefix	id
1	1
1	2
2	1
2	2

instead of the

prefix	id
1	1
1	2
2	3
2	4

which it currently produces.

And not having to rely on lock tables and calculating next increment values for the current prefix in code. Also using multiple tables for each prefix would be a maintenance nightmare.

Any ideas how this may be done?

I read something about the SEQUENCE Storage Engine. Not sure if/how that would work for this use case.

*I read something about the SEQUENCE Storage Engine. Not sure if/how that would work for this use case.* No, it simply provides extended sequences support. — Akina, Dec 27 '21 at 10:00
You might want to check other questions like https://stackoverflow.com/questions/677542/auto-increment-by-group, https://stackoverflow.com/questions/9626358/auto-increment-column-for-a-group-of-rows and https://stackoverflow.com/questions/38737506/how-to-add-auto-increment-id-according-to-a-group-in-mysql. Also check for the "MyISAM" database storage format, which have a native support for that (however you will loose a lot of features if you use "MyISAM"). — Progman, Dec 27 '21 at 12:35
@Progman thanks for pointing this out. Nostalgic, I almost forgot about the good old MyISAM engine. For this particular table I could live with some of the "loss of features". It does solve the problem above. I'll do some benchmark tests to check performance though. I can totally accept this as a solution for my requirement. — Vikash, Jan 09 '22 at 15:09

score 0 · Answer 1 · answered Dec 27 '21 at 17:53

When inserting the values, the id column (auto_increment) should always populated with NULL values, so all rows can be ordered by id.

Then using IF together with some user variables will do the job:

CREATE TABLE ...
INSERT INTO testing VALUES (1,NULL),(1,NULL),(2, NULL), (2,NULL), (4, NULL);
SET @lastprefix:=-1;
select prefix, if(@lastprefix!=prefix,@id:=(@lastprefix:=prefix)> 0,@id:=@id+1) as id from testing order by prefix,id;

Mariadb auto_increment different series for another column

1 Answers1