mysql 批量修改视图定义者 DEFINER

原文： MySQL如何批量修改视图(VIEW)的属主(DEFINER)
参考： Mysql The user specified as a definer ('test'@'%') does not exist

在mysql数据库的迁移、备份恢复等操作时，有时会遇到错误：The user specified as a definer ('sdc'@'%') does not exist
这是视图的DEFINER属性造成的，修改即可：

1、生成修改语句

sql> select concat("alter DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `",TABLE_SCHEMA,"`.",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where TABLE_SCHEMA = 'test' AND DEFINER = 'sdc@%';

结果.png

2、复制执行即可