Creating VIEWs in MySQL

January 3, 2008

The MySQL VIEW acts like a virtual table. Each VIEW is actually a SQL statement that has been previously defined.

To create a VIEW:

CREATE VIEW view_name AS
sql_statement;

A basic VIEW could be created as the following:

CREATE VIEW Cities AS
SELECT     DISTINCT(City)
FROM       Customers
ORDER BY   City ASC;

When we run a SHOW COLUMNS FROM Cities MySQL returns the following:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| city  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.16 sec)

To access the information from the newly created VIEW:

SELECT     City
FROM       Cities
LIMIT      5;

And MySQL returns:

+----------------+
| city           |
+----------------+
| 100 MILE HOUSE |
| ABBOTSFORD     |
| ABBOTT PARK    |
| ACHESON        |
| ACTON          |
+----------------+
5 rows in set (0.09 sec)

I know this example is incredibly simple, but I hope you can see the potential for using VIEWs. The creation syntax for VIEWs also includes the capability to assign an algorithm type (either AUTO, MERGE or TEMPTABLE) and there are some limitations (not being able to access user variables). The example that I have included is the most simplified method for creating a new view.

With the MySQL VIEWs I am able to simplify other-wise complicated SQL statements. To generate summary level details (by using several aggregate functions) and JOIN that information to more readable SQL, I am saving myself a lot of debugging and troubleshooting if future modifications are required.

For example if I was working on a bulletin board application and I want to display a summary for a user:

CREATE VIEW user_summary AS
SELECT     Users.UserID,
           COUNT(Comments) AS user_comment_count,
           COUNT(Posts) AS user_post_count
FROM       Users LEFT JOIN
           (
            Comments LEFT JOIN Posts
            ON
             (
              Comments.PostID = Posts.ID
             )
            )
            ON
             (
              Users.UserID = Comments.UserID
             )
GROUP BY   Users.UserID;

Now I have a VIEW that contains the bulletin board summary information for the users. To JOIN the VIEW to another SQL statment:

SELECT    Users.UserID,
          user_summary.user_comment_count,
          user_summary.user_post_count
FROM      Users LEFT JOIN user_summary
          ON
           (
            Users.UserID = user_summary.UserID
           );

If you have any questions, or would like further clarification. Please leave me a comment and I will answer you promptly.

Advertisements