mysql动态列表_如何在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

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.

解决方案

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 |

+-------+----+