mysql 存储过程

关于MySQL存储过程，视图，事件

查询修改存储过程

select `name` from mysql.proc where `type` = 'PROCEDURE';
select `name` from mysql.proc where `type` = 'FUNCTION';

select definer from mysql.proc where definer like '%demo%';
select DEFINER from mysql.EVENTS;
select DEFINER from information_schema.VIEWS;   
select DEFINER from information_schema.TRIGGERS;

SHOW CREATE PROCEDURE Proo_Bm_CreateMessageData;

手动执行存储过程

mysql> DROP PROCEDURE IF EXISTS `Proo_Bm_CreateMessageData`;
......
mysql> grant all privileges on *.* to root@"%" identified by ".";
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter //
mysql> CREATE DEFINER=`root`@`%` PROCEDURE `Prou_Bm_PlanStatusChange`()
BEGIN-- 声明变量
	DECLARE
		row_id BIGINT ( 20 );
		...
		...
		...
END;
//
Query OK, 0 rows affected (0.00 sec)

mysql> 

注意

关于delimiter MySQL默认结束是分号；但是，delimiter 可以自定义结束符号。例如：

DELIMITER $$ --先定义结束符

​ DROP TRIGGER IF EXISTS triggername $$

​ CREATE TRIGGER triggername AFTER DELETE ON id

​ FOR EACH ROW BEGIN

​ …
​ END； – 遇到；mysql解释器不再执行

$$--遇到$$ 开始执行

DELIMITER ; – 还原结束符

存储过程的导入导出

导出：

mysqldump -u 数据库用户名 -p -n -t -d -R --triggers=false 数据库名 > 文件名

-d 表示–no-create-db,

-n表示–no-data

-t表示–no-create-info,

-R表示导出function和procedure

–triggers=false

-ntd 是表示导出存储过程

-R是表示导出函数

导入：

mysqldump -uroot -p -ntd -R demo > sql.sql
mysqldump -h127.0.0.1 -uroot -p -ntd -R --triggers=false demo > demo.sql

导入前，确认sql文件中 definer ！！！ 导入时所用账号 并不具有 SUPER 权限，所以无法创建 其他账号 的所属对象。
如果需要修改definer为新的库名
利用vi命令全局替换
:1,$ s/oldDBName/newDBName/g

mysql -h127.0.0.1 -uroot -p demo < demo.sql

The user specified as a definer (‘root’@’%’) does not exist（没有权限）

1. 先看下mysql是否设置了环境变量，如果没设置，就需要切换到mysql的bin目录下执行命令（我的已经设置过了）

mysql -hlocalhost -uroot -p123456

2. 给root用户添加权限

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

3. 刷新权限

flush privileges;

附加：

MySQL 中一个很特别的权限控制机制，像视图、函数、存储过程、触发器等这些数据对象会存在一个 DEFINER 和一个 SQL SECURITY 的属性，如下所示：

--视图定义
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test

--函数定义
CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER

--存储过程定义
CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER

--触发器定义
CREATE DEFINER=`root`@`%` trigger t_test 

--事件定义
CREATE DEFINER=`root`@`%` EVENT `e_test`
1234567891011121314

• DEFINER：对象定义者，在创建对象时可以手动指定用户，不指定的话默认为当前连接用户；
• SQL SECURITY：指明以谁的权限来执行该对象，有两个选项，一个为DEFINER，一个为INVOKER，默认情况下系统指定为 DEFINER；
  • DEFINER：表示按 定义者 的权限来执行；
  • INVOKER：表示按 调用者 的权限来执行。

如果导入账号具有 SUPER 权限，即使对象的所有者账号不存在，也可以导入成功，但是在查询对象时，如果对象的 SQL SECURITY 为 DEFINER，则会报账号不存在的报错。

ERROR 1449 (HY000): The user specified as a definer (‘root’@’%’) does not exist