I’ve found that I often create several views that are variations on an initial one, providing a slightly different convenient slice of the data as the need arises. But when a view is created the syntax actually stored can be quite different than what you put in. The parser reorganizes and rewrites to make everything explicit. While better for performance in running the view, this has the unfortunate side effect of transmuting a once readable statement into a forest of parentheses and backticks that can and does induce tears.
First an example view that I created using the handy world database:
1 2 3 4 5 6 7 8 | CREATE VIEW world.low_central_luzon AS SELECT a.Name AS "City", b.Name AS "Country", a.Population, a.District FROM world.City a JOIN world.Country b ON (a.CountryCode = b.Code) WHERE b.Continent = "Asia" AND a.District = "Central Luzon" ORDER BY a.Population LIMIT 10; |
This is a view of the 10 cites with the lowest population in the Central Luzon region of Asia. Now let’s see what actually got stored. To get a view’s create statement you can use the MySQL Query Browser and right click the view name shown in the right under Schemata, selecting “Copy SQL to Clipboard”. Or you can run a query like:
1 2 3 | SELECT VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_NAME = "low_central_luzon" AND TABLE_SCHEMA = "world"; |
Here’s the result for the view above:
1 2 3 4 5 | SELECT `a`.`Name` AS `City`,`b`.`Name` AS `Country`,`a`.`Population` AS `Population`,`a`.`District` AS `District` FROM (`world`.`City` `a` JOIN `world`.`Country` `b` ON((`a`.`CountryCode` = `b`.`Code`))) WHERE ((`b`.`Continent` = 'Asia') AND (`a`.`District` = 'Central Luzon')) ORDER BY `a`.`Population` LIMIT 10 |
I added the line breaks for a modicum of readablilty, the statement is stored without them. I sure don’t want to edit that version in making a variant! This example isn’t actually too bad, but for real world complicated views, it really is quite difficult to read views exported in this way. I often try to store the original create view query just to avoid this pain. But sometimes I only have the stored syntax.
I’ve created a Bash function to make this whole process less painful:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | function mysqldump_view { # Grabs the create statement for the view passed in the db passed. # Performs some text cleanup to make it more human readable, outputs # to file. USAGE="USAGE: mysqldump_view DATABASE_NAME VIEW_NAME" opt_err="Must pass db and view." if [ $# -ne 2 ] then echo $opt_err echo $USAGE return 1 fi DB=$1 VIEW=$2 DEFAULT_USER="root" OUTPUT_FILE="view-$VIEW.sql" echo "Attempting to connect with mysql defaults, user $DEFAULT_USER..." mysql -u $DEFAULT_USER -p --skip-column-names -e "SELECT VIEW_DEFINITION FROM \ information_schema.VIEWS where TABLE_NAME = \"$VIEW\" and TABLE_SCHEMA = \"$DB\";" \ | sed -e "1s/^\(.*\)$/CREATE VIEW \"$VIEW\" as\n\1/" \ -e 's/`,`/`,\n`/g' \ -e 's/ from /\nfrom /g' \ -e 's/ join /\n\tjoin /g' \ -e 's/ where /\nwhere /g' \ -e 's/ and /\n\tand /g' \ -e 's/ limit /\nlimit /g' \ -e 's/ order /\norder /g' \ -e '$s/$/;/' > $OUTPUT_FILE && echo "Done. View syntax written to $OUTPUT_FILE." return 0 } |
Now to get a cleaned up view you can call this function followed by the database and view names:
To get the create statements for multiple views you can use a loop. For example:
1 | for VIEW in "high_central_luzon" "low_central_luzon"; do mysqldump_view world $VIEW; done |
If you need some more flexibility than this I implemented it in Python after an initial fit of rage caused by trying to get various levels of nested strings properly escaped in Bash. That ended up being overly long and unnecessary, and a cooler head prevailed. But the script is here on GitHub if you want it. It creates the same file although without using sed and with some error handling.
You can save yourself some typing with SHOW CREATE VIEW world.low_central_luzon
SHOW CREATE TABLE is also handy when to get a column definition for ALTER TABLE … CHANGE COLUMN. (You have to repeat the full definition even when just renaming or moving a column.)
@deltab That is shorter to simply get at the create statement, but what you get back is still ugly and hard to read. That’s why I wrote the function above. And invoking it ends up being shorter than that command anyway