oracle中可以直接用Create Sequence创建序列。mysql只能自己手动创建了,过程如下:
- 建表
CREATE TABLE `sequence` ( `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '序列的名字', `current_value` int(11) NOT NULL COMMENT '序列的当前值', `increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值', PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
- 创建初始化序列,读取当前值,读取下一个值并回写三个函数:
CREATE DEFINER=`root`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11) DETERMINISTICBEGIN DECLARE value INTEGER; SET value = 0; SELECT current_value INTO value FROM sequence WHERE name = seq_name; RETURN value; END
CREATE DEFINER=`root`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11) DETERMINISTICBEGIN UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name; RETURN currval(seq_name); END
CREATE DEFINER=`root`@`%` FUNCTION `setval`(seq_name VARCHAR(50), value INTEGER) RETURNS int(11) DETERMINISTICBEGIN UPDATE sequence SET current_value = value WHERE name = seq_name; RETURN currval(seq_name); END
这样就ok了,测试sql如下: