Filed under MySQL | by Samuel Huckins | Date Posted: November 15, 2009 - 11:23 PM

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:
example

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.

Share and Enjoy: