MySQL中Sequence的使用

Oracle中Sequence定义和使用

Oracle中有Sequence序列生成器用于生成表的主键值，官方定义：Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables.

 sequence cache：Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). Sequence numbers can be accessed more quickly in the sequence cache than they can be read from disk.When a sequence is read into the sequence cache, sequence values are generated and stored in a cache entry. The number of sequence values stored in the cache is determined by the CACHE parameter in the CREATE SEQUENCE statement. The default value for this parameter is 20.

CREATE SEQUENCE emp_sequence
      INCREMENT BY 1
      START WITH 1
      NOMAXVALUE
      NOCYCLE
      CACHE 10;

MySQL中定义和使用Sequence

CREATE TABLE `sequence` (

  `seq_name` varchar(50) NOT NULL,

  `current_value` int(11) NOT NULL,

  `increment_value` int(11) NOT NULL DEFAULT '1',

  PRIMARY KEY (`seq_name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1、func_currval：获取当前序列的值并返回

CREATE FUNCTION `func_currval`(seq_name varchar(50))

 RETURNS int(11)

begin

  declare value integer;

  set value = 0;

  select current_value into value  

  from sequence 

  where seq_name = seq_name limit 1;

  return value;

end;

2、func_setval：设置当前序列的值并返回

CREATE FUNCTION `func_setval`(seq_name varchar(50))

 RETURNS int(11)

begin

 update sequence

 set current_value = current_value + increment_value

 where seq_name = seq_name;

 return func_currval(seq_name);

end;

3、func_nextval：获取序列中的一个可用的值

CREATE FUNCTION `func_nextval`(seq_name varchar(50),value integer)

 RETURNS int(11)

begin

 update sequence

 set current_value = value

 where seq_name = seq_name;

 return func_currval(seq_name);

end;

INSERT INTO sequence VALUES ('seq_xxx, '0', '1');

SELECT func_nextval(''seq_xxx');

SELECT func_currval(''seq_xxx');

使用Auto-Increments(MySQL官方出品)

1、创建一个序列表：

CREATE TABLE `sequence` (

  `id` bigint(20) unsigned NOT NULL auto_increment,

  `stub` char(1) NOT NULL default '',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `stub` (`stub`)

) ENGINE=MyISAM;

SELECT * from sequence ;

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

| id                | stub |

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

| 72157623227190423 |    a |

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

2、如果需要一个新的值，执行以下的SQL获取：

REPLACE INTO sequence(stub) VALUES ('a');

SELECT LAST_INSERT_ID();

使用UUID或UUID_SHORT(MySQL官方出品)

UUID_SHORT()：

Returns a “short” universal identifier as a 64-bit unsigned integer. Values returned by UUID_SHORT() differ from the string-format 128-bit identifiers returned by the UUID() function and have different uniqueness properties. The value of UUID_SHORT() is guaranteed to be unique if the following conditions hold:

The UUID_SHORT() return value is constructed this way: (server_id & 255) << 56 + (server_startup_time_in_seconds << 24) + incremented_variable++;

mysql> SELECT UUID_SHORT();  -> 92395783831158784 

总结

以上三个方案均可以实现序列功能，但高并发场景肯定会存在性能问题（Oracle有序列缓存），要结合具体业务场景评估使用，建议以下等价方案：

1. 使用自增INT类型作为物理主键，MySQL底层存储为B+ Tree；
2. 借助第三方基础服务实现，如：分布式ID生成器SowFlake；
3. 使用UUID或者UUID_SHORT函数生成唯一值；

美团的技术博客介绍比较详细：Leaf——美团点评分布式ID生成系统

参考

 sequence cache

MySQL实现类似Oracle的序列 - sequence

Leaf——美团点评分布式ID生成系统

Ticket Servers: Distributed Unique Primary Keys on the Cheap