Mysql数据库创建自增序列

• 创建序列表

CREATE TABLE `sequence` (`name` varchar(50) NOT NULL,`current_value` bigint(30) NOT NULL,`increment` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='序列表';

• 创建函数 查询当前序列名的序列值

CREATE DEFINER=`root`@`%` FUNCTION `shop-product`.`currval`(seq_name VARCHAR(50)) RETURNS bigint(30)DETERMINISTIC
BEGINDECLARE vCurrent BIGINT(30);  SET vCurrent = 0;  SELECT current_value INTO vCurrent FROM sequence WHERE NAME = seq_name;   RETURN vCurrent; 
END

• 创建函数 查询当前序列名下一个序列值

CREATE DEFINER=`root`@`%` FUNCTION `shop-product`.`nextval`(seq_name VARCHAR(50)) RETURNS bigint(30)DETERMINISTIC
BEGIN  
UPDATE sequence SET current_value = current_value + increment WHERE NAME = seq_name;  
RETURN currval(seq_name);  
END

• 创建函数 更新当前序列名的序列值

CREATE DEFINER=`root`@`%` FUNCTION `shop-product`.`setval`(seq_name VARCHAR(50),value BIGINT) RETURNS bigint(30)DETERMINISTIC
BEGINUPDATE sequence  SET current_value = value WHERE name = seq_name; RETURN currval(seq_name); 
END

• 序列表插入数据

INSERT INTO `sequence`
(name, current_value, `increment`)
VALUES('test', 1, 1);

使用方法

SELECT SETVAL(‘test’, 10); --设置指定sequence的初始值
SELECT CURRVAL(‘test’); --查询指定sequence的当前值
SELECT NEXTVAL(‘test’); --查询指定sequence的下一个值