mysql 实现nextval_mysql实现nextVal功能

首先创建表：

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`)

)

插入记录

INSERT INTO SYS_SEQUENCE(NAME,CURRENT_VALUE,INCREMENT) VALUES('TBL_FS', 1,1)

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 ;

查询当前记录：

select currval('TBL_FS');如果出现Error Code: 1449 The user specified as a definer ('root'@'%') does not exist

错误，则执行如下sql：

grant all privileges on *.* to root@"%" identified by "password";

flush privileges;

继续执行当前记录sql则成功

创建nextval函数

DELIMITER $$

DROP FUNCTION IF EXISTS `nextval`$$

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$$

执行nextval函数

select nextval('tbl_fs')