进城务工人员小梅对于MySQL而言，视图的定义中有三个重要的参数：
|
1 2 3 4 5 6 7 8 |
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] |
援引官方文档：
The optional ALGORITHM clause for CREATE VIEW or ALTER VIEW is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED.
- For
MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement. - For
TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement. - For
UNDEFINED, MySQL chooses which algorithm to use. It prefersMERGEoverTEMPTABLEif possible, becauseMERGEis usually more efficient and because a view cannot be updatable if a temporary table is used. - If no
ALGORITHMclause is present, the default algorithm is determined by the value of thederived_mergeflag of theoptimizer_switchsystem variable.
The DEFINER and SQL SECURITY clauses determine which MySQL account to use when checking access privileges for the view when a statement is executed that references the view. The valid SQL SECURITY characteristic values are DEFINER (the default) and INVOKER. These indicate that the required privileges must be held by the user who defined or invoked the view, respectively.
If the DEFINER clause is present, the user value should be a MySQL account specified as ', user_name'@'host_name'CURRENT_USER, or CURRENT_USER().
我们可以通过如下的SQL生成批量将视图的SQL SECURITY修改为INVOKER的SQL：
|
1 2 |
SELECT CONCAT("ALTER SQL SECURITY INVOKER VIEW `",TABLE_NAME,"` AS ", VIEW_DEFINITION,";") FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='test' |
我们可以通过如下的SQL生成批量修改视图DEFINER的SQL：
|
1 2 |
SELECT CONCAT("ALTER DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," AS ",VIEW_DEFINITION,";") FROM INFORMATION_SCHEMA.VIEWS WHERE DEFINER != 'root@%'; |
参考资料：
1、https://dev.mysql.com/doc/refman/8.0/en/create-view.html
2、https://dev.mysql.com/doc/refman/8.0/en/alter-view.html
3、https://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html
转载时请保留出处，违法转载追究到底：进城务工人员小梅 » MySQL批量修改视图的ALGORITHM、DEFINER、SECURITY定义