1、建表
CREATE TABLE 'sys_sequence' (
'NAME' VARCHAR(50) NOT NULL,
'CURRENT_VALUE' INT(11) NOT NULL DEFAULT '0',
'INCREMENT' INT(11) NOT NULL DEFAULT '1',
PRIMARY KEY('NAME')
)
2、建立函数
DELIMITER $$
DROP FUNCTION IF EXISTS `currval`$$
CREATE DEFINER=`root`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS INT(11)
BEGIN
DECLARE VALUE INTEGER;
SET VALUE=0;
SELECT current_value INTO VALUE
FROM sys_sequence
WHERE NAME=seq_name;
RETURN VALUE;
END$$
DELIMITER ;
CREATE DEFINER=`root`@`%` FUNCTION `nextval`(seq_name varchar(50)) RETURNS int(11)
BEGIN
UPDATE sys_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)
BEGIN
update sys_sequence
set current_value=value
where name=seq_name;
return currval(seq_name);
END