mysql视图和存储过程定义者修改脚本（懒人专用）

前言： 在实际工作中mysql数据库的迁移、备份恢复、数据库重命名等一系列涉及到视图和存储过程定义者问题都会需要修改，每次都要从基础表获取数据，然后手工整理做脚本，十分麻烦，所以简单写了个过程，以后可以更加方便的迁移后更新定义者问题了，下面是解决过程~

 

-- 失败第一个版本 及失败原因

CREATE DEFINER = CURRENT_USER()PROCEDURE CHANGE_DEFINER(    `pr_database_name` VARCHAR(500), -- 数据库名称    `pr_definer_name` VARCHAR(500), -- 定义者名称    `pr_definer_ip_name` VARCHAR(500)-- 定义者绑定的ip，默认为 % ，任意ip)BEGINDECLARE SQL_CHANGE_DEFINER longtext;DECLARE DATABASE_NAME VARCHAR(500);DECLARE DEFINER_NAME VARCHAR(500) DEFAULT CURRENT_USER();DECLARE DEFINER_IP_NAME VARCHAR (500) DEFAULT '%';SET DATABASE_NAME = TRIM(pr_database_name);IF pr_definer_name IS NOT NULL AND LENGTH(pr_definer_name)>0 THENSET DEFINER_NAME = TRIM(pr_definer_name);END IF;IF pr_definer_ip_name IS NOT NULL AND LENGTH(pr_definer_ip_name) > 0 THENSET    DEFINER_IP_NAME = pr_definer_ip_name;END IF;-- 组装修改视图定义者语句IF DATABASE_NAME IS NOT NULL AND LENGTH(DATABASE_NAME)>0 THEN -- group_concat默认查询结果长度1024,长度不足,设置成足够长度如下SET GLOBAL group_concat_max_len=1024000;SET SESSION group_concat_max_len=1024000;  SELECT    GROUP_CONCAT(        ' alter definer = `',        DEFINER_NAME,        '`@`' ,DEFINER_IP_NAME, '` view ',        TABLE_NAME,        ' as ',        VIEW_DEFINITION,        ';' SEPARATOR ''    ) INTO SQL_CHANGE_DEFINERFROM    information_schema.VIEWSWHERE    TABLE_SCHEMA = DATABASE_NAMEGROUP BY    TABLE_SCHEMA;  -- 执行修改视图定义者  SET @VALUE = CONCAT(SQL_CHANGE_DEFINER);  PREPARE stmt FROM @VALUE;  EXECUTE stmt;  -- 修改存储过程定义者  UPDATE mysql.proc  set DEFINER = CONCAT(DEFINER_NAME,'@',DEFINER_IP_NAME) WHERE db = DATABASE_NAME; ELSE  SELECT '数据库名称不允许为空';END IF;ENDmysql不支持问题代码

对于这个版本是由于prepare stmt from 语句，这个语句只能是单独的语句，而我却 alter ...; alter ...; 肯定不行了，改成每一个单独就好了吧，继续改

-- 失败第二个版本 及失败原因

ALTER TABLEALTER USER (as of MySQL 5.6.8)ANALYZE TABLECACHE INDEXCALLCHANGE MASTERCHECKSUM {TABLE | TABLES}COMMIT{CREATE | RENAME | DROP} DATABASE{CREATE | DROP} INDEX{CREATE | RENAME | DROP} TABLE{CREATE | RENAME | DROP} USER{CREATE | DROP} VIEWDELETEDOFLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES| LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}GRANTINSERTINSTALL PLUGINKILLLOAD INDEX INTO CACHEOPTIMIZE TABLEREPAIR TABLEREPLACERESET {MASTER | SLAVE | QUERY CACHE}REVOKESELECTSETSHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS}SHOW BINLOG EVENTSSHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}SHOW {MASTER | BINARY} LOGSSHOW {MASTER | SLAVE} STATUSSLAVE {START | STOP}TRUNCATE TABLEUNINSTALL PLUGINUPDATE

这里还有一句话，Other statements are not supported in MySQL 5.6. 擦汗啊，这么简单个脚本一波三折，万幸看到了drop view，和 create view 这不就直接等于alter view了吗？

就这么改，于是乎终于成功了，如下：

CREATE DEFINER = CURRENT_USER()PROCEDURE my_apm.CHANGE_DEFINER(    `pr_database_name` VARCHAR(500), -- 数据库名称    `pr_definer_name` VARCHAR(500), -- 定义者名称    `pr_definer_ip_name` VARCHAR(500)-- 定义者绑定的ip，默认为 % ，任意ip)BEGINDECLARE drop_view_ varchar(500);DECLARE create_view_ varchar(15000);DECLARE DATABASE_NAME VARCHAR(500);DECLARE DEFINER_NAME VARCHAR(500) DEFAULT CURRENT_USER();DECLARE DEFINER_IP_NAME VARCHAR (500) DEFAULT '%';DECLARE flag boolean DEFAULT 1;DECLARE cur CURSOR FOR SELECT drop_view,create_view FROM sql_value;DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 0;DROP TEMPORARY TABLE IF EXISTS sql_value; CREATE TEMPORARY TABLE sql_value(drop_view varchar(500),create_view varchar(15000));SET DATABASE_NAME = TRIM(pr_database_name);IF pr_definer_name IS NOT NULL AND LENGTH(pr_definer_name)>0 THENSET DEFINER_NAME = TRIM(pr_definer_name);END IF;IF pr_definer_ip_name IS NOT NULL AND LENGTH(pr_definer_ip_name) > 0 THENSET    DEFINER_IP_NAME = pr_definer_ip_name;END IF;-- 组装修改视图定义者语句IF DATABASE_NAME IS NOT NULL AND LENGTH(DATABASE_NAME)>0 THEN   INSERT INTO sql_value(drop_view,create_view)  SELECT  GROUP_CONCAT('drop view if exists ',TABLE_NAME,';'),    GROUP_CONCAT('create definer = `',        DEFINER_NAME,        '`@`' ,DEFINER_IP_NAME, '` view ',        TABLE_NAME,        ' as ',        VIEW_DEFINITION,        ';' SEPARATOR ''    )FROM    information_schema.VIEWSWHERE    TABLE_SCHEMA = DATABASE_NAMEGROUP BY    TABLE_NAME;  -- 执行修改视图定义者OPEN cur;rep:LOOP  FETCH cur INTO drop_view_,create_view_;  set @drop_view_ = drop_view_;  set @create_view_ = create_view_;  IF flag = 0 THEN     LEAVE rep;  END IF;   PREPARE stmt FROM @drop_view_;   EXECUTE stmt;   DEALLOCATE PREPARE stmt;   PREPARE stmt FROM @create_view_;   EXECUTE stmt;   DEALLOCATE PREPARE stmt;END LOOP;CLOSE cur;-- 修改存储过程定义者UPDATE mysql.proc  set DEFINER = CONCAT(DEFINER_NAME,'@',DEFINER_IP_NAME) WHERE db = DATABASE_NAME AND NAME != 'CHANGE_DEFINER'; ELSE  SELECT '数据库名称不允许为空';END IF;END

上面过程可能有哪里不合理的地方，欢迎指正

文章转自http://www.cnblogs.com/jiangwenju/p/5607173.html