MySQL Views / Stoyan's phpied.com

Continuing (from here) the "Discover MySQL's enterprise features" trip, let's check out the Views.

As the name suggests, a view is like a specific predefined way to look at a table or tables. You can create a View out of one table or you can JOIN a few tables. Back to the `blog` database example.

Creating a `posts` table and inserting some data in it.

mysql> CREATE TABLE posts (
    -> id INT(11) not null auto_increment primary key,
    -> title char(255),
    -> body text
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO posts VALUES
    -> ('','title 1', 'body 1'),
    -> ('','title 2', 'body 2'),
    -> ('','title 3', 'body 3'),
    -> ('','title 4', 'body 4'),
    -> ('','title 5', 'body 5'),
    -> ('','title 6', 'body 6')
    -> ;
Query OK, 6 rows affected, 6 warnings (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 6

Is the data there?

mysql> SELECT * FROM posts;
+----+---------+--------+
| id | title   | body   |
+----+---------+--------+
|  1 | title 1 | body 1 |
|  2 | title 2 | body 2 |
|  3 | title 3 | body 3 |
|  4 | title 4 | body 4 |
|  5 | title 5 | body 5 |
|  6 | title 6 | body 6 |
+----+---------+--------+
6 rows in set (0.00 sec)

Now we're interested in the last 5 posts (we want to create an RSS feed or display in the "see also" nav or something similar)

mysql> SELECT * FROM posts ORDER BY id DESC LIMIT 0,5;
+----+---------+--------+
| id | title   | body   |
+----+---------+--------+
|  6 | title 6 | body 6 |
|  5 | title 5 | body 5 |
|  4 | title 4 | body 4 |
|  3 | title 3 | body 3 |
|  2 | title 2 | body 2 |
+----+---------+--------+
5 rows in set (0.00 sec)

Good, it works. Now let's create a view using only ID and title.

mysql> CREATE VIEW last_posts AS
    -> SELECT id, title FROM posts ORDER BY id DESC LIMIT 0,5;
Query OK, 0 rows affected (0.01 sec)

Yep, that easy! Now we can SELECT from the view as if it was a normal table.

mysql> SELECT * FROM last_posts;
+----+---------+
| id | title   |
+----+---------+
|  6 | title 6 |
|  5 | title 5 |
|  4 | title 4 |
|  3 | title 3 |
|  2 | title 2 |
+----+---------+
5 rows in set (0.00 sec)

The similarity with a table goes even further, the view will SHOW up as a table...

mysql> SHOW TABLES;
+----------------+
| Tables_in_blog |
+----------------+
| last_posts     |
| posts          |
+----------------+
2 rows in set (0.00 sec)

(BTW, SHOW FIELDS FROM last_posts will also work)

... but you can always tell if a SHOWn table is a table or a view ...

mysql> SHOW FULL TABLES;
+----------------+------------+
| Tables_in_blog | Table_type |
+----------------+------------+
| last_posts     | VIEW       |
| posts          | BASE TABLE |
+----------------+------------+
2 rows in set (0.00 sec)

... and you can get only views if you want to.

mysql> SHOW FULL TABLES WHERE Table_type = 'VIEW';
+----------------+------------+
| Tables_in_blog | Table_type |
+----------------+------------+
| last_posts     | VIEW       |
+----------------+------------+
1 row in set (0.00 sec)

The views you create will be displayed in phpMyAdmin as well.

A view can be used in JOINs as if it was a normal table.

mysql> SELECT last_posts.*, posts.body FROM last_posts
    -> LEFT JOIN posts ON
    -> last_posts.id = posts.id;
+----+---------+--------+
| id | title   | body   |
+----+---------+--------+
|  6 | title 6 | body 6 |
|  5 | title 5 | body 5 |
|  4 | title 4 | body 4 |
|  3 | title 3 | body 3 |
|  2 | title 2 | body 2 |
+----+---------+--------+
5 rows in set (0.00 sec)

mysql> SELECT last_posts.*, CONCAT(SUBSTRING(posts.body,1,3), '...')
    -> AS excerpt
    -> FROM last_posts
    -> LEFT JOIN posts ON
    -> last_posts.id = posts.id;
+----+---------+---------+
| id | title   | excerpt |
+----+---------+---------+
|  6 | title 6 | bod...  |
|  5 | title 5 | bod...  |
|  4 | title 4 | bod...  |
|  3 | title 3 | bod...  |
|  2 | title 2 | bod...  |
+----+---------+---------+
5 rows in set (0.01 sec)

And, as said in the beginnig, you can create a view from a table JOIN. Or even from joining a table and another view.

mysql> CREATE VIEW last_posts_details AS
    -> SELECT last_posts.*, CONCAT(SUBSTRING(posts.body,1,3), '...')
    -> AS excerpt
    -> FROM last_posts
    -> LEFT JOIN posts ON
    -> last_posts.id = posts.id;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM last_posts_details;
+----+---------+---------+
| id | title   | excerpt |
+----+---------+---------+
|  6 | title 6 | bod...  |
|  5 | title 5 | bod...  |
|  4 | title 4 | bod...  |
|  3 | title 3 | bod...  |
|  2 | title 2 | bod...  |
+----+---------+---------+
5 rows in set (0.00 sec)

mysql> muahaah-haha!
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'muaha
ah-haha!' at line 1
mysql>

Although a view shows up in SHOW TABLES, you cannot drop it with DROP TABLE, you need DROP VIEW.