mysql创建函数sequence+[Err] 1449 -The user specified as a definer (root @ %) does not exist解决方法

原创已于 2025-01-02 13:37:38 修改 · 963 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2020-05-29 17:35:41 首次发布

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何在MySQL中通过创建特殊表和函数实现类似Oracle的序列功能，包括获取当前值、下一个值及更新值的方法。

Oracle中有sequence可以方便取递增值，mysql中没有，可以通过以下方式实现

1、创建一个表管理多个sequence

DROP TABLE IF EXISTS sequence; 
CREATE TABLE sequence ( 
	name VARCHAR(50) NOT NULL, 
	current_value INT NOT NULL, 
	increment INT NOT NULL DEFAULT 1, 
	PRIMARY KEY (name) 

) ENGINE=InnoDB;

在这里插入图片描述
2、创建函数，取当前值

DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM sequence
          WHERE name = seq_name; 
     RETURN value; 
END
$ 

DELIMITER ;

3、创建函数取下一个值

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     UPDATE sequence
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END
$ 

DELIMITER ;

4、创建函数更新当前值

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     UPDATE sequence
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END
$ 

DELIMITER ;

在这里插入图片描述

5、下面是测试

INSERT INTO sequence VALUES ('TestSeq', 0, 1);----添加一个sequence名称和初始值，以及自增幅度

在这里插入图片描述

SELECT SETVAL('TestSeq', 10);---设置指定sequence的初始值

在这里插入图片描述

SELECT CURRVAL('TestSeq');--查询指定sequence的当前值

在这里插入图片描述

SELECT NEXTVAL('TestSeq');--查询指定sequence的下一个值

在这里插入图片描述
【definer和invoker的解释】

创建存储过程的时候可以指定 SQL SECURITY属性，设置为 DEFINER 或者INVOKER，用来奉告mysql在执行存储过程的时候，，是以DEFINER用户的权限来执行，还是以调用者的权限来执行。

默认情况下，使用DEFINER方式，此时调用存储过程的用户必须有存储过程的EXECUTE权限，并且DEFINER指定的用户必须是在mysql.user表中存在的用户。

DEFINER模式下，默认DEFINER=CURRENT_USER，在存储过程执行时，mysql会检查DEFINER定义的用户’user_name’@'host_name’的权限；

INVOKER模式下，在存储过程执行时，会检查存储过程调用者的权限。

在特定用户下，查询sequence的下一个值，会提示一下错误

The user specified as a definer ('root'@'%') does not exist

在这里插入图片描述
因为DEFINER模式下创建函数，默认DEFINER=CURRENT_USER

比如我这里使用root用户创建的函数，定义者这里显示root@%，如果这时候将这个数据库sql脚本，在另外一个用户guest下执行，使用guest用户调用这个函数（SELECT NEXTVAL(‘TestSeq’);），就会出现权限问题。
在这里插入图片描述