你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2013-08-02] 来源:Linux社区  作者:dba_waterbin [字体: ]

视图本质上只是一条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'),
'\nsource=',-1),
'\\_','\_'), '\\%','\%'), '\\\\','\\'), '\\Z','\Z'), '\\t','\t'),
'\\r','\r'), '\\n','\n'), '\\b','\b'), '\\\"','\"'), '\\\'','\''),
'\\0','\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 name

the file to be loaded must be located in that directory

相关阅读

MySQL视图表创建与修改 http://www.linuxidc.com/Linux/2012-02/53842.htm

MySQL视图(view) http://www.linuxidc.com/Linux/2013-07/87158.htm

linux
相关资讯       SQL语句  MySQL视图 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款