MySQL实现nextval功能
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.插入一条模拟表的数据
INSERT INTO SYS_SEQUENCE(NAME,CURRENT_VALUE,INCREMENT) VALUES('TBL_TEMPLATE', 1,1)
3.实现currval函数
1.执行函数方法失败(临时生效,重启后失效)解决办法
set global log_bin_trust_function_creators=TRUE;
2.在配置文件 my.ini 的 [mysqld] 配置,永久生效
set global log_bin_trust_function_creators=1
3.再执行以下函数
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 ;
4.查询该函数是否生效
select currval('TBL_TEMPLATE');
5.更新用户权限并刷新权限
1.低版本数据库操作
grant all privileges on *.* to root@"%" identified by "password";
flush privileges;
遇以下问题请使用高版本解决
报错:
mysql> grant all privileges on *.* to "root"@"%" identified by "xxxxx";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by xxxxx
查看数据库版本,高版本数据库不能按照grant all privileges on . to “root”@“%” identified by “xxxx”;去修改用户权限
2.高版本数据库操作
1.查看数据库版本
SELECT @@VERSION;
2.执行以下方法更新和刷新权限
create user 'root'@'%' identified by 'password';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
6.创建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$$
7.测试nextval函数
select nextval('TBL_TEMPLATE')