借用MySQL 的 auto_increment 特性可以产生唯一的可靠ID。
表定义,关键在于auto_increment,和UNIQUE KEY的设置:
CREATE TABLE `Tickets64` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`stub` char(1) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM
需要使用时,巧用replace into语法来获取值,结合表定义的UNIQUE KEY,确保了一条记录就可以满足ID生成器的需求:
REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();
It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.
需要注意的是,若client采用PHP,则不能使用mysql_insert_id()获取ID,原因见《mysql_insert_id() 在bigint型AI字段遇到的问题》:http://kaifage.com/notes/99/mysql-insert-id-issue-with-bigint-ai-field.html。