Views Work In MySQL 5.0

By Peter Gulutzan

On July 15 2004, Oleksandr Byelkin of Ukraine merged his
"views" code into MySQL. I have verified that the MySQL version 5.0
Linux source download supports views. This report will interest people who know
MySQL, who know what views are, and who have been waiting a long time for MySQL
to support views.

INSTALLING

I downloaded MySQL 5.0 source as described in the manual "2.3.3
Installing from the Development Source Tree
" and built a new package
on a Linux machine that I use for tests.

I started the mysqld server, started a client as user =
root, and ran this initial check:

mysql> SELECT * FROM mysql.db;

I saw two new columns named Create_view_priv and Show_view_priv.
(If these columns are not present, then some mysql tables need fixing and the MySQL
Reference Manual 2.5.8 "Upgrading the
Grant Tables
" page has the necessary instructions.)

I gave myself privileges by changing the columns:

mysql> UPDATE mysql.db SET Create_view_priv='Y', Show_view_priv='Y';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

I made a sample table in the test database:

mysql> USE test;
Database changed

mysql> CREATE TABLE table1 (s1 INT, s2 CHAR(30));
Query OK, 0 rows affected (0.29 sec)

mysql> INSERT INTO table1 VALUES (1,'Row #1');
Query OK, 1 row affected (0.00 sec)

Then I was ready to test.

TEST #1

mysql> CREATE VIEW v1 AS SELECT * FROM table1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+----------------+------------+
| Tables_in_test | table_type |
+----------------+------------+
| table1         |BASE TABLE  |
| v1             |VIEW        |
+----------------+------------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE VIEW v1;

+-------+-------------------------------+
+                                       |
| Table | Create Table                  |
|       |                               |
+-------+-------------------------------+
| v1    | CREATE VIEW test.v1 AS select |
|       | 'test'.'table1'.'s1' AS 's1', |
|       | 'test'.'table1'.'s2' AS 's2   |
|       | from 'test'.'table1'          |
+-------+-------------------------------|

This test showed that CREATE VIE W works. Clearly one can
make an object using the standard CREATE VIEW syntax, and that object will be
visible with SHOW statements.

TEST #2

mysql> SELECT * FROM v1;
+------+--------+
| s1   | s2     |
+------+--------+
|    1 | Row #1 |
+------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO v1 VALUES (5,'Row #2');
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM v1 WHERE s1 = 5;
Query OK, 1 row affected (0.29 sec)

mysql> INSERT INTO v1 VALUES (2,'Row #2');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE v1 SET s2 = 'Row #1-A' WHERE s1 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT s2, s1 FROM v1;
+----------+------+
| s2       | s1   |
+----------+------+
| Row #1-A |    1 |
| Row #2   |    2 |
+----------+------+
2 rows in set (0.00 sec)

This test showed that views are truly windows through which
we see the underlying base tables. Clearly one can change the original table,
or see its contents, by issuing data-manipulation statements against the view.

The fact that simple views are updatable is a feature that
we’re proud of — as the MySQLmanual says, our eventual goal is that theoretically-updatable
views should be updatable in practice.

TEST #3

mysql> CREATE VIEW v2 AS select CONCAT(s1,s2) AS v2_s1
    -> FROM v1
    -> WHERE s1 = 1 OR s1 = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('*',v2_s1,'*')
    -> FROM v2
    -> WHERE v2_s1 <> 2
    -> ORDER BY v2_s1;
+-----------------------+
| CONCAT('*',v2_s1,'*') |
+-----------------------+
| *1Row #1-A*           |
+-----------------------+
1 row in set (0.26 sec)

This test showed miscellaneous capabilities. Clearly one can
make views of views, use view columns in expressions, and use WHERE clauses in
both the view and the outer query.

The above tests suffice for demonstration. You can find a
much more severe test series in the source download, in the file ~/mysql-5.0/mysql-test/t/views.text.

RULES

Since views are only a few weeks old, these rules are
subject to change without notice. A better description will appear in the MySQL Reference
Manual when the time is ripe.

1. The CREATE VIEW syntax looks like:

   CREATE [ OR REPLACE ] VIEW view-name [(column-list)]
   AS select-statement
   [ WITH CHECK OPTION ]

The view-name is an identifier; like other
identifiers it may be delimited, may be up to 64 characters long, and may be qualified.

The select-statement may contain almost
any SELECT syntax that’s legal in MySQL,
including joins, subqueries, unions,
AS clauses, or references to other views.
However, it may not contain references to temporary
tables, parameters, or variables.

2. The SHOW CREATE VIEW statement looks like:

   SHOW CREATE VIEW view-name

This statement is like SHOW CREATE TABLE, SHOW CREATE PROCEDURE, and so on.

3. The DROP VIEW statement looks like:

   DROP VIEW view-name [ RESTRICT | CASCADE ]

4. The ALTER VIEW statement looks like this:

   ALTER VIEW view-name
   AS select-statement

5. The GRANT CREATE VIEW statement looks like:

   GRANT CREATE VIEW ON database-name . * TO user-name

There is a new column in the mysql.db table, Create_view_priv,
which depends on GRANT CREATE VIEW.

6. Simple views are updatable, that is, they may be used in
UPDATE, INSERT, DELETE statements. There are restrictions, such
as: the view’s SELECT may not contain DISTINCT, GROUP BY, expressions in the select
list, or other things which make a view non-updatable in the
theoretical sense.

7. There is a new variable, sql_updatable_view_key, with
possible values: ‘YES’ (meaning "issue error"), ‘NO’ ("don’t
issue error but issue warning"), and ‘LIMIT1’ ("issue error only if LIMIT 1 is
used"). This controls checking of keys in underlying tables.

8. The GRANT statement for tables works for views too.
So "GRANT SELECT ON v1 TO Sam;" means
that Sam can SELECT from view v1. However, that does not
mean that Sam can SELECT from the table that
underlies v1. A view privilege applies only to the view.

Peter Gulutzan lives in Edmonton, Canada and works for MySQL
AB. He holds official MySQL Certification. Peter co-authored
four computer books; the latest was SQL Performance Tuning. He plans to speak
at MySQL’s next user conference in April 2005 in Santa Clara.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles