Extensions to
SHOW
Statements
1729
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 13.1.12,
“
CREATE VIEW
Syntax”
.)
• The
DEFINER
and
SECURITY_TYPE
columns were added in MySQL 5.0.14.
DEFINER
indicates who
defined the view.
SECURITY_TYPE
has a value of
DEFINER
or
INVOKER
.
MySQL lets you use different
sql_mode
[495]
settings to tell the server the type of SQL syntax to
support. For example, you might use the
ANSI
[540]
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
[495]
setting to a
value different from
ANSI
[540]
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()
[887]
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
[495]
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.
19.18. Extensions to
SHOW
Statements
Some extensions to
SHOW
statements accompany the implementation of
INFORMATION_SCHEMA
:
•
SHOW
can be used to get information about the structure of
INFORMATION_SCHEMA
itself.
• Several
SHOW
statements accept a
WHERE
clause that provides more flexibility in specifying which
rows to display.
These extensions are available beginning with MySQL 5.0.3.
INFORMATION_SCHEMA
is an information database, so its name is included in the output from
SHOW
DATABASES
. Similarly,
SHOW TABLES
can be used with
INFORMATION_SCHEMA
to obtain a list of its
tables:
mysql>
SHOW TABLES FROM INFORMATION_SCHEMA;
+---------------------------------------+
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...