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

DECLARE 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 THEN
SET DEFINER_NAME = TRIM(pr_definer_name);
END IF;
IF pr_definer_ip_name IS NOT NULL AND LENGTH(pr_definer_ip_name) > 0 THEN
SET    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_DEFINER
FROM
    information_schema.VIEWS
WHERE
    TABLE_SCHEMA = DATABASE_NAME
GROUP 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;

END

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

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

CREATE DEFINER = 'harri'@'%'
PROCEDURE my_apm.CHANGE_DEFINER(
    `pr_database_name` VARCHAR(500), -- 数据库名称
    `pr_definer_name` VARCHAR(500), -- 定义者名称
    `pr_definer_ip_name` VARCHAR(500)-- 定义者绑定的ip，默认为 % ，任意ip
)
BEGIN

DECLARE SQL_CHANGE_DEFINER longtext;
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 value_ FROM sql_value;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 0;

DROP TEMPORARY TABLE IF EXISTS sql_value; 
CREATE TEMPORARY TABLE sql_value(value_ varchar(15000));


SET DATABASE_NAME = TRIM(pr_database_name);

IF pr_definer_name IS NOT NULL AND LENGTH(pr_definer_name)>0 THEN
SET DEFINER_NAME = TRIM(pr_definer_name);
END IF;
IF pr_definer_ip_name IS NOT NULL AND LENGTH(pr_definer_ip_name) > 0 THEN
SET    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(value_)
  SELECT
    GROUP_CONCAT(
        ' alter definer = `',
        DEFINER_NAME,
        '`@`' ,DEFINER_IP_NAME, '` view ',
        TABLE_NAME,
        ' as ',
        VIEW_DEFINITION,
        ';' SEPARATOR ''
    )
FROM
    information_schema.VIEWS
WHERE
    TABLE_SCHEMA = DATABASE_NAME
GROUP BY
    TABLE_NAME;

  -- 执行修改视图定义者
OPEN cur;

rep:LOOP

  FETCH cur INTO SQL_CHANGE_DEFINER;
  set @VALUE = SQL_CHANGE_DEFINER;
  IF flag = 0 THEN 
    LEAVE rep;
  END IF;

   PREPARE stmt FROM @VALUE;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
END LOOP;

CLOSE cur;
  -- 修改存储过程定义者

  UPDATE mysql.proc  set DEFINER = CONCAT(DEFINER_NAME,'@',DEFINER_IP_NAME) WHERE db = DATABASE_NAME;
 
ELSE
  SELECT '数据库名称不允许为空';
END IF;

END

感觉稳稳的了，结果出乎意料，看来prepare 支持的语句掌握不牢靠，竟然不支持 alter view， 为什么alter table都支持还差view了吗，于是乎去查mysql 5.6官方手册，支持的语句如下：

ALTER TABLE
ALTER USER (as of MySQL 5.6.8)
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
| LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE

这里还有一句话，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
)
BEGIN

DECLARE 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 THEN
SET DEFINER_NAME = TRIM(pr_definer_name);
END IF;
IF pr_definer_ip_name IS NOT NULL AND LENGTH(pr_definer_ip_name) > 0 THEN
SET    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.VIEWS
WHERE
    TABLE_SCHEMA = DATABASE_NAME
GROUP 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

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