#创建序列表 DROP TABLE IF EXISTS `sequence`; CREATE TABLE `sequence` ( `name` varchar(50) NOT NULL COMMENT '序列名称', `current_value` bigint(20) NOT NULL COMMENT '序列当前值', `increment` int(11) NOT NULL DEFAULT '1' COMMENT '步长', PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '序列表'; #查询序列当前值 DELIMITER $$ CREATE #DEFINER=`root`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS bigint(20) DETERMINISTIC BEGIN DECLARE value VARCHAR(20); SET value = 0; SELECT current_value INTO value FROM sequence WHERE name = seq_name; RETURN value; END$$ DELIMITER ; #取下一个序列号 DELIMITER $$ CREATE #DEFINER=`root`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS bigint(20) DETERMINISTIC BEGIN UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name;#更新序列号 RETURN currval(seq_name);#取当前序列号 END$$ DELIMITER ; #为序列赋值(初始化序列) DELIMITER $$ CREATE #DEFINER=`root`@`%` FUNCTION `setval`(seq_name VARCHAR(50), value BIGINT) RETURNS bigint(20) DETERMINISTIC BEGIN #初始化更新 UPDATE sequence SET current_value = value WHERE name = seq_name; #返回序列当前值 RETURN currval(seq_name); END$$ DELIMITER ;