创建视图的sql包含了一个load_file()函数、为了使用该函数、必须满足下面所有条件：
视图本质上只是一条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)
，