Mysql修改mysql的视图定义者

一、查看所有视图的库名、表名、定义者

select TABLE_SCHEMA,TABLE_NAME,DEFINER from information_schema.VIEWS;

 二、生成alter VIEW的SQL

select concat("alter DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER = 'poc4a@%';

 其中poc4a@%为原视图定义者，root@%为需要修改的新的视图定义者

 三、执行生成的修改DEFINER语句

 复制上面执行结果中想要修改的库表视图的结果语句

如：

 修改后查看：