MySQL Views

December 11th, 2006. Tagged: mysql

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.

More in the MySQL manual's Views entry.

Tell your friends about this post on Facebook and Twitter

Sorry, comments disabled and hidden due to excessive spam.

Meanwhile, hit me up on twitter @stoyanstefanov