突破MySQL视图限制：获取创建视图的SQL语句

视图本质上只是一条SQL语句而已、但令人蛋疼的是MySQL并没有把该SQL语句存储下来
而是像对待表一样、把视图的定义用文件的形式保存、以 .frm 存在
那么用show create view 显示的SQL将非常不友好下面介绍一种方法来突破这种限制创建视图：mysql> create view v_t as select id from t where id=2;
Query OK, 0 rows affected （0.03 sec）到相应目录查找视图定义文件：[mysql@obe11g test]$ pwd
/home/mysql/mysql/data/test
[mysql@obe11g test]$ ls -alh
total 128K
drwxr-xr-x 2 mysql dba 4.0K Jul 27 19:45 .
drwxr-xr-x 5 mysql dba 4.0K Jul 27 19:13 ..
-rw-r--r-- 1 mysql dba 65 Jun 19 10:20 db.opt
-rw-rw---- 1 mysql dba 8.4K Jul 24 19:58 t.frm
-rw-rw---- 1 mysql dba 96K Jul 27 19:44 t.ibd
-rwxrwxrwx 1 mysql dba 451 Jul 27 19:45 v_t.frm
先用 show create view查询：mysql> show create view v_t;
+------+----------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_t | CREATE ALGORITHM=UNDEFINED DEFINER=`waterbin`@`localhost` SQL SECURITY DEFINER VIEW `v_t` AS select `t`.`id` AS `id` from `t` where （`t`.`id` = 2） | utf8 | utf8_general_ci |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set （0.00 sec）会发现包含大量转义符、引号、没有代码格式化、没有注释、没有缩进等等、可读性很差、无法快速拷贝进行重建视图查询创建视图的SQL语句：SELECT
REPLACE（REPLACE（REPLACE（REPLACE（REPLACE（REPLACE（
REPLACE（REPLACE（REPLACE（REPLACE（REPLACE（
SUBSTRING_INDEX（LOAD_FILE（"/home/mysql/mysql/data/test/v_t.frm"）,
" source=",-1）,
"\_","\_"）, "\%","\%"）, "\\","\"）, "\Z",""）, "\t"," "）,
"\r"," "）, "\n"," "）, "\b",""）, "\"","""）, "\"","""）,
"\0",""）
AS source;输出结果、第一行便是该SQL：+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| source |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select id from t where id=2
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `test`.`t`.`id` AS `id` from `test`.`t` where （`test`.`t`.`id` = 2）
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set （0.00 sec）创建视图的SQL包含了一个load_file（）函数、为了使用该函数、必须满足下面所有条件：① the file must be located on the server host② you must specify the full path name to the file③ you must have the FILE privilege验证：select user,file_priv from mysql.user;④ The file must be readable by all提醒：这里的all、不仅是OWNER、GROUP；还特指OTHERE！！⑤ its size less than max_allowed_packet bytes⑥ If the secure_file_priv system variable is set to a nonempty directory namethe file to be loaded must be located in that directory相关阅读：MySQL视图表创建与修改 http://www.linuxidc.com/Linux/2012-02/53842.htmMySQL视图（view） http://www.linuxidc.com/Linux/2013-07/87158.htmLinux HugePages及MySQL 大页配置诊断Oracle RMAN备份慢的原因相关资讯 SQL语句 MySQL视图

如何定位SQL语句在共享池里用到了（03月17日）
MySQL 存储过程动态执行sql语句（10/13/2015 19:10:08）
画图解释 SQL join 语句（07/17/2015 15:16:27）

MySQL中的视图（01月16日）
Java 注解入门自动生成SQL语句（07/28/2015 16:08:34）
Oracle 通过sql profile为sql语句（05/03/2015 19:43:07）

本文评论查看全部评论（0）