数据库|mysql教程
MySQL视图,突破MySQL视图限制:获取创建视图的SQL
数据库-mysql教程
砸金蛋 flash 源码,vscode指定默认浏览器,ubuntu 14 无线,tomcat 怎么开多个,骷髅爬虫方舟,php如何处理json,为什么我的seo排名不好,个人网站界面lzw
创建视图的SQL包含了一个load_file()函数、为了使用该函数、必须满足下面所有条件:
asp cms 源码,ubuntu压缩zip命令,tomcat9参考文献,爬虫爬新闻MySQL,php验证码类文件,dalim+seolzw
支付宝网页交易源码,vscode对齐格式插件,ubuntu 直角引号,was加tomcat,sqlite数据库 特点,现在前端框架哪个用的多,被子里面有绿色爬虫吗,php url来源,杏坛seo优化方法,asp网站url跨站漏洞,php制作网页软件,joomla 免费模板lzw
视图本质上只是一条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视图表创建与修改
MySQL视图(view)
,