突破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 -alhtotal 128Kdrwxr-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語句：SELECTREPLACE(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=2client_cs_name=utf8connection_cl_name=utf8_general_ciview_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

By DBA_WaterBin

2013-07-28

GOOD Luck