mysql 批量修改多个已有view中数据库名前缀的方法

mysql 批量修改多个已有view中数据库名前缀的方法

1、在目标数据中创建一个如下代码所示存储过程。注意修改 `root`@`%` 为你自己的root用户

CREATE DEFINER=`root`@`%` PROCEDURE `update_views_dbname_suffix`(IN old_dbname varchar(30), IN new_dbname varchar(30) )
BEGIN
	DECLARE sql_field VARCHAR ( 1000000 );
	# 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;
	# 游标
	DECLARE cur_view CURSOR FOR SELECT
	CONCAT( 'alter DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` AS ', REPLACE ( VIEW_DEFINITION, old_dbname, new_dbname ),';' ) AS a 
FROM
	information_schema.VIEWS 
WHERE
	`TABLE_SCHEMA` = new_dbname 
	AND POSITION( old_dbname IN `VIEW_DEFINITION` ) > 0;
	
  # 将结束标志绑定到游标
  DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	SET @asql = '';
	SET @n = 0; 
	# 打开游标
  OPEN cur_view;
  # 遍历
  REPEAT
  # 取值
		FETCH NEXT FROM cur_view INTO sql_field;
    IF not done THEN 
			SET @asql = concat(@asql , sql_field);
      SET @n = @n + 1;			
    END IF;   
  UNTIL done END REPEAT;
  CLOSE cur_view;
	SELECT @n as alter_views, @asql as alter_sql;
END

 

2、调用它，传入原数据库名和新数据库名（也就是当前数据库名）。

调用代码示例：

call update_views_dbname_suffix('test_nmg','nmg')

返回的alter_views表示找到了符合条件的view的个数，返回的alter_sql是alter view的语句，是alter_views个拼在一起的。

形如下图：

3、将第2步返回的alter_sql复制到执行窗口中，运行它，在执行结果区域，会看到alter_views个执行OK的提示，则修改完成。

4、设计模式打开你的view，检查成果吧！