How to generate a dynamic sequence table in MySQL?

I’m trying to generate a sequence table in MySQL, so that I can get unique ids from last_insert_id.

The problem is that I need multiple sequences dynamically.


At the first, I created a table:

CREATE TABLE `sequence` (
  `label` char(30) CHARACTER SET latin1 NOT NULL,
  `id` mediumint(9) NOT NULL DEFAULT '0',
  PRIMARY KEY (`label`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And then tried to get the number, using example from http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

UPDATE sequence SET id = LAST_INSERT_ID(id + 1) WHERE label = 'test';
SELECT LAST_INSERT_ID();

After a while I realized that I also need to generate rows for new labels safely.
So I changed this schema into:

CREATE TABLE `sequence` (
  `label` char(30) CHARACTER SET latin1 NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`label`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And I simply gave up using WHERE clause to update its id.

INSERT INTO sequence (label) values ( ? )
SELECT LAST_INSERT_ID()

Is this a proper way? I want to know if there is a better solution.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The MyISAM engine will do it for you –

Table definition:

CREATE TABLE `sequence` (
  `label` char(30) CHARACTER SET latin1 NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`label`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Populate table:

INSERT INTO sequence VALUES ('a', NULL); -- add some 'a' labels
INSERT INTO sequence VALUES ('a', NULL);
INSERT INTO sequence VALUES ('a', NULL);

INSERT INTO sequence VALUES ('b', NULL); -- add another labels 'b'
INSERT INTO sequence VALUES ('b', NULL);

INSERT INTO sequence VALUES ('a', NULL); -- add some 'a' labels
INSERT INTO sequence VALUES ('a', NULL);

Show result:

SELECT * FROM sequence;
+-------+----+
| label | id |
+-------+----+
| a     |  1 |
| a     |  2 |
| a     |  3 |
| a     |  4 |
| a     |  5 |
| a     |  6 |
| b     |  1 |
| b     |  2 |
+-------+----+


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x