The VIEWS
table provides information
about views in databases. You must have the
SHOW VIEW
privilege to access this
table.
INFORMATION_SCHEMA
Name |
SHOW
Name |
Remarks |
TABLE_CATALOG |
def |
|
TABLE_SCHEMA |
||
TABLE_NAME |
||
VIEW_DEFINITION |
||
CHECK_OPTION |
||
IS_UPDATABLE |
||
DEFINER |
||
SECURITY_TYPE |
||
CHARACTER_SET_CLIENT |
MySQL extension | |
COLLATION_CONNECTION |
MySQL extension |
Notes:
The VIEW_DEFINITION
column has most of what
you see in the Create Table
field that
SHOW CREATE VIEW
produces. Skip
the words before SELECT
and
skip the words WITH CHECK OPTION
. Suppose
that the original statement was:
CREATE VIEW v AS SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1 WITH CHECK OPTION;
Then the view definition looks like this:
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
The CHECK_OPTION
column has a value of
NONE
, CASCADE
, or
LOCAL
.
MySQL sets a flag, called the view updatability flag, at
CREATE VIEW
time. The flag is
set to YES
(true) if
UPDATE
and
DELETE
(and similar operations)
are legal for the view. Otherwise, the flag is set to
NO
(false). The
IS_UPDATABLE
column in the
VIEWS
table displays the status
of this flag. It means that the server always knows whether a
view is updatable. If the view is not updatable, statements
such UPDATE
,
DELETE
, and
INSERT
are illegal and will be
rejected. (Note that even if a view is updatable, it might not
be possible to insert into it; for details, refer to
Section 12.1.16, “CREATE VIEW
Syntax”.)
The DEFINER
column indicates who defined
the view. SECURITY_TYPE
has a value of
DEFINER
or INVOKER
.
CHARACTER_SET_CLIENT
is the session value
of the character_set_client
system variable when the view was created.
COLLATION_CONNECTION
is the session value
of the collation_connection
system variable when the view was created.
MySQL lets you use different
sql_mode
settings to tell the
server the type of SQL syntax to support. For example, you might
use the ANSI
SQL mode to ensure
MySQL correctly interprets the standard SQL concatenation
operator, the double bar (||
), in your queries.
If you then create a view that concatenates items, you might worry
that changing the sql_mode
setting to a value different from
ANSI
could cause the view to
become invalid. But this is not the case. No matter how you write
out a view definition, MySQL always stores it the same way, in a
canonical form. Here is an example that shows how the server
changes a double bar concatenation operator to a
CONCAT()
function:
mysql>SET sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec) mysql>CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;
Query OK, 0 rows affected (0.00 sec) mysql>SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
->WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
+----------------------------------+ | VIEW_DEFINITION | +----------------------------------+ | select concat('a','b') AS `col1` | +----------------------------------+ 1 row in set (0.00 sec)
The advantage of storing a view definition in canonical form is
that changes made later to the value of
sql_mode
will not affect the
results from the view. However an additional consequence is that
comments prior to SELECT
are
stripped from the definition by the server.
User Comments
Theese queries may come in handy:
# list all views
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS;
# describe view
# replace ? with view name
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE
TABLE_NAME = ?;
# describe view prettyprinted
# replace ? with view name
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(VIEW_DEFINITION, ",", "\n"),
"from", "\nfrom"), "where", "\nwhere"), "join", "\njoin"), "and", "\nand")
FROM INFORMATION_SCHEMA.VIEWS
WHERE
TABLE_NAME = ?;
Add your own comment.