posts - 496,comments - 227,trackbacks - 0
http://lztian.com/blog/5921.html

借用MySQL 的 auto_increment 特性可以产生唯一的可靠ID。

表定义,关键在于auto_increment,和UNIQUE KEY的设置:

1
2
3
4
5
6
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生成器的需求:

1
2
REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();

以上方式中,通过MySQL的机制,可以确保此ID的唯一和自增,且适用于多并发的场景。官方对此的描述:https://dev.mysql.com/doc/refman/5.0/en/information-functions.html

1
2
3
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。

Flickr 采取了此方案: http://code.flickr.net/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/

相关:

http://www.zhihu.com/question/30674667

http://my.oschina.net/u/142836/blog/174465

posted on 2016-06-28 18:48 SIMONE 阅读(239) 评论(0)  编辑  收藏 所属分类: mysql

只有注册用户登录后才能发表评论。


网站导航: