Mysql之修改mysql的视图定义者

转载网址：

https://blog.csdn.net/carefree2005/article/details/109812943?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1.pc_relevant_antiscanv2&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1.pc_relevant_antiscanv2&utm_relevant_index=1

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

    mysql>select TABLE_SCHEMA,TABLE_NAME,DEFINER from information_schema.VIEWS;

二、生成alter VIEW的SQL

使用Navicat Premium 12（数据库连接客户端工具）执行如下查询语句，其中test@%为原视图定义者，root@localhost为需要修改的新的视图定义者。

    select concat(“alter DEFINER=‘root’@‘localhost’ SQL SECURITY DEFINER VIEW “,TABLE_SCHEMA,”.”,TABLE_NAME," as “,VIEW_DEFINITION,”;") from information_schema.VIEWS where DEFINER = ‘test@%’;

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

复制生成的SQL语句并执行