问题描述
我正在尝试在MySQL中生成序列表,以便可以从last_insert_id
获取唯一ID.
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
然后尝试使用 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
我只是放弃了使用WHERE
子句来更新其ID.
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.
推荐答案
MyISAM引擎将为您完成-
The MyISAM engine will do it for you -
表定义:
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;
填充表:
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);
显示结果:
SELECT * FROM sequence;
+-------+----+
| label | id |
+-------+----+
| a | 1 |
| a | 2 |
| a | 3 |
| a | 4 |
| a | 5 |
| a | 6 |
| b | 1 |
| b | 2 |
+-------+----+
这篇关于如何在MySQL中生成动态序列表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!