Breaking through MySQL view restrictions: Obtain SQL statements for creating views

In essence, a view is only an SQL statement, but MySQL does not store the SQL statement.
Instead, we save the view definition in the form of a file like a table, and use. frm to exist.
The SQL statement displayed with show create view is unfriendly.

The following describes a method to break through this restriction.

Create a view: mysql> Create view v_t as select ID from t where id = 2; query OK, 0 rows affected (0.03 Sec) to the corresponding directory to find the view definition file: [MySQL @ obe11g test] $ PWD/home/MySQL/data/test [MySQL @ obe11g test] $ LS-alhtotal 128kdrwxr-xr-x 2 MySQL DBA 4.0 K Jul 27. drwxr-XR-x 5 MySQL DBA 4.0 K Jul 27 :19:13 .. -RW-r -- 1 MySQL DBA 65 Jun 19 10:20 dB. opt-RW ---- 1 MySQL DBA 8.4 K Jul 24 19:58 T. FRM-RW ---- 1 MySQL DBA 96 k Jul 27 19: 44 t. IBD-rwxrwxrwx 1 MySQL DBA 451 Jul 27 v_t.frm first use show create view to query: mysql> show create view v_t; + ------ + custom metrics + | View | create View | character_set_client | collation_connection | + ------ + metrics + ------------------ + | 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 | + ------ + bytes --------------------- + ------------------------ + ------------------ + 1 row in SET (0.00 Sec) you can find SQL statements that contain a large number of escape characters, quotation marks, no code formatting, no comments, no indentation, and so on. They are not readable and cannot be quickly copied to recreate views. query and create views: selectreplace (replace (substring_index (load_file ('/home/MySQL/data/test/v_t.frm' ), '\ nsource =',-1), '\ _', '\ _'), '\ %', '\ % '), '\', '\'), '\ Z',' \ Z'), '\ t',' \ t '), '\ R',' \ R'), '\ n',' \ n'), '\ B', '\ B '), '\ "', '\"'), '\', '\'), '\ 0', '\ 0') as source; the first line of the output result is the SQL statement: + ------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------ + | 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) | + response ------------------------------------------------ + 1 row in SET (0.00 Sec)

The SQL statement used to create a view contains a load_file () function. To use this function, all the following conditions must be met:

① 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
Verification: Select User, file_priv from mysql. user;
④ The file must be readable by all
Reminder: all, not only owner and group, but also 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