1.创建一个储值表:存储每次自增后的最新值,用于读取
CREATE TABLE `sequence` (
`name` VARCHAR(50) NOT NULL, //自增名
`current_value` INT(11) NOT NULL, //自增的最新值
`increment` INT(11) NOT NULL DEFAULT '1' //每次的增量
)
2.创建自增函数:
参数:自增表的name
返回:下一个自增值
CREATE DEFINER=`root`@`%` FUNCTION `sequence_nextval`(
`seq_name` VARCHAR(50)
)
RETURNS int(11)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '获取下一个值'
BEGIN
DECLARE VALUE INTEGER;
SET VALUE = 0;
UPDATE sequence SET current_value = current_value + increment WHERE NAME = seq_name;
SELECT current_value INTO VALUE FROM sequence WHERE NAME = seq_name;
RETURN VALUE;
END
3.调用:
SELECT sequence_nextval(#{name})
4.示例:
查询:
欢迎加Q讨论:361528529