|
|
Table of Contents
INFORMATION_SCHEMA SCHEMATA
TableINFORMATION_SCHEMA TABLES
TableINFORMATION_SCHEMA COLUMNS
TableINFORMATION_SCHEMA STATISTICS
TableINFORMATION_SCHEMA USER_PRIVILEGES
TableINFORMATION_SCHEMA SCHEMA_PRIVILEGES
TableINFORMATION_SCHEMA TABLE_PRIVILEGES
TableINFORMATION_SCHEMA COLUMN_PRIVILEGES
TableINFORMATION_SCHEMA CHARACTER_SETS
TableINFORMATION_SCHEMA COLLATIONS
TableINFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY
TableINFORMATION_SCHEMA TABLE_CONSTRAINTS
TableINFORMATION_SCHEMA KEY_COLUMN_USAGE
TableINFORMATION_SCHEMA ROUTINES
TableINFORMATION_SCHEMA VIEWS
TableINFORMATION_SCHEMA TRIGGERS
TableINFORMATION_SCHEMA
TablesSHOW
Statements
INFORMATION_SCHEMA
provides access to database
metadata.
Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.
INFORMATION_SCHEMA
is the information database,
the place that stores information about all the other databases that
the MySQL server maintains. Inside
INFORMATION_SCHEMA
there are several read-only
tables. They are actually views, not base tables, so there are no
files associated with them.
In effect, we have a database named
INFORMATION_SCHEMA
, although the server does not
create a database directory with that name. It is possible to select
INFORMATION_SCHEMA
as the default database with a
USE
statement, but it is possible only to read
the contents of tables. You cannot insert into them, update them, or
delete from them.
Here is an example of a statement that retrieves information from
INFORMATION_SCHEMA
:
mysql>SELECT table_name, table_type, engine
->FROM information_schema.tables
->WHERE table_schema = 'db5'
->ORDER BY table_name DESC;
+------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | v56 | VIEW | NULL | | v3 | VIEW | NULL | | v2 | VIEW | NULL | | v | VIEW | NULL | | tables | BASE TABLE | MyISAM | | t7 | BASE TABLE | MyISAM | | t3 | BASE TABLE | MyISAM | | t2 | BASE TABLE | MyISAM | | t | BASE TABLE | MyISAM | | pk | BASE TABLE | InnoDB | | loop | BASE TABLE | MyISAM | | kurs | BASE TABLE | MyISAM | | k | BASE TABLE | MyISAM | | into | BASE TABLE | MyISAM | | goto | BASE TABLE | MyISAM | | fk2 | BASE TABLE | InnoDB | | fk | BASE TABLE | InnoDB | +------------+------------+--------+ 17 rows in set (0.01 sec)
Explanation: The statement requests a list of all the tables in
database db5
, in reverse alphabetical order,
showing just three pieces of information: the name of the table, its
type, and its storage engine.
Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges.
The SELECT ... FROM INFORMATION_SCHEMA
statement
is intended as a more consistent way to provide access to the
information provided by the various SHOW
statements that MySQL supports (SHOW DATABASES
,
SHOW TABLES
, and so forth). Using
SELECT
has these advantages, compared to
SHOW
:
It conforms to Codd's rules. That is, all access is done on tables.
Nobody needs to learn a new statement syntax. Because they
already know how SELECT
works, they only need
to learn the object names.
The implementor need not worry about adding keywords.
There are millions of possible output variations, instead of just one. This provides more flexibility for applications that have varying requirements about what metadata they need.
Migration is easier because every other DBMS does it this way.
However, because SHOW
is popular with MySQL
employees and users, and because it might be confusing were it to
disappear, the advantages of conventional syntax are not a
sufficient reason to eliminate SHOW
. In fact,
along with the implementation of
INFORMATION_SCHEMA
, there are enhancements to
SHOW
as well. These are described in
Section 20.18, “Extensions to SHOW
Statements”.
There is no difference between the privileges required for
SHOW
statements and those required to select
information from INFORMATION_SCHEMA
. In either
case, you have to have some privilege on an object in order to see
information about it.
The implementation for the INFORMATION_SCHEMA
table structures in MySQL follows the ANSI/ISO SQL:2003 standard
Part 11 Schemata. Our intent is approximate
compliance with SQL:2003 core feature F021 Basic
information schema.
Users of SQL Server 2000 (which also follows the standard) may
notice a strong similarity. However, MySQL has omitted many columns
that are not relevant for our implementation, and added columns that
are MySQL-specific. One such column is the ENGINE
column in the INFORMATION_SCHEMA.TABLES
table.
Although other DBMSs use a variety of names, like
syscat
or system
, the standard
name is INFORMATION_SCHEMA
.
The following sections describe each of the tables and columns that
are in INFORMATION_SCHEMA
. For each column, there
are three pieces of information:
“INFORMATION_SCHEMA
Name”
indicates the name for the column in the
INFORMATION_SCHEMA
table. This corresponds to
the standard SQL name unless the “Remarks” field
says “MySQL extension.”
“SHOW
Name” indicates the
equivalent field name in the closest SHOW
statement, if there is one.
“Remarks” provides additional information where
applicable. If this field is NULL
, it means
that the value of the column is always NULL
.
If this field says “MySQL extension,” the column is
a MySQL extension to standard SQL.
To avoid using any name that is reserved in the standard or in DB2,
SQL Server, or Oracle, we changed the names of some columns marked
“MySQL extension”. (For example, we changed
COLLATION
to TABLE_COLLATION
in the TABLES
table.) See the list of reserved
words near the end of this article:
http://www.dbazine.com/gulutzan5.shtml.
The definition for character columns (for example,
TABLES.TABLE_NAME
) is generally
VARCHAR(
where N
) CHARACTER SET
utf8N
is at least 64.
Each section indicates what SHOW
statement is
equivalent to a SELECT
that retrieves information
from INFORMATION_SCHEMA
, if there is such a
statement.
Note: At present, there are some missing columns and some columns out of order. We are working on this and update the documentation as changes are made.
A schema is a database, so the SCHEMATA
table
provides information about databases.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
CATALOG_NAME | NULL | |
SCHEMA_NAME | Database | |
DEFAULT_CHARACTER_SET_NAME | ||
DEFAULT_COLLATION_NAME | ||
SQL_PATH | NULL |
Notes:
DEFAULT_COLLATION_NAME
was added in MySQL
5.0.6.
The following statements are equivalent:
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA [WHERE SCHEMA_NAME LIKE 'wild
'] SHOW DATABASES [LIKE 'wild
']
The TABLES
table provides information about
tables in databases.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
TABLE_CATALOG | NULL | |
TABLE_SCHEMA | Table_ ... | |
TABLE_NAME | Table_ ... | |
TABLE_TYPE | ||
ENGINE | Engine | MySQL extension |
VERSION | Version | MySQL extension |
ROW_FORMAT | Row_format | MySQL extension |
TABLE_ROWS | Rows | MySQL extension |
AVG_ROW_LENGTH | Avg_row_length | MySQL extension |
DATA_LENGTH | Data_length | MySQL extension |
MAX_DATA_LENGTH | Max_data_length | MySQL extension |
INDEX_LENGTH | Index_length | MySQL extension |
DATA_FREE | Data_free | MySQL extension |
AUTO_INCREMENT | Auto_increment | MySQL extension |
CREATE_TIME | Create_time | MySQL extension |
UPDATE_TIME | Update_time | MySQL extension |
CHECK_TIME | Check_time | MySQL extension |
TABLE_COLLATION | Collation | MySQL extension |
CHECKSUM | Checksum | MySQL extension |
CREATE_OPTIONS | Create_options | MySQL extension |
TABLE_COMMENT | Comment | MySQL extension |
Notes:
TABLE_SCHEMA
and
TABLE_NAME
are a single field in a
SHOW
display, for example
Table_in_db1
.
TABLE_TYPE
should be BASE
TABLE
or VIEW
. If table is
temporary, then TABLE_TYPE
=
TEMPORARY
. (There are no temporary views,
so this is not ambiguous.)
The TABLE_ROWS
column is
NULL
if the table is in the
INFORMATION_SCHEMA
database. For
InnoDB
tables, the row count is only a
rough estimate used in SQL optimization.
We have nothing for the table's default character set.
TABLE_COLLATION
is close, because collation
names begin with a character set name.
The following statements are equivalent:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES [WHERE table_schema = 'db_name
'] [WHERE|AND table_name LIKE 'wild
'] SHOW TABLES [FROMdb_name
] [LIKE 'wild
']
The COLUMNS
table provides information about
columns in tables.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
TABLE_CATALOG | NULL | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | Field | |
ORDINAL_POSITION | see notes | |
COLUMN_DEFAULT | Default | |
IS_NULLABLE | Null | |
DATA_TYPE | Type | |
CHARACTER_MAXIMUM_LENGTH | Type | |
CHARACTER_OCTET_LENGTH | ||
NUMERIC_PRECISION | Type | |
NUMERIC_SCALE | Type | |
CHARACTER_SET_NAME | ||
COLLATION_NAME | Collation | |
COLUMN_TYPE | Type | MySQL extension |
COLUMN_KEY | Key | MySQL extension |
EXTRA | Extra | MySQL extension |
COLUMN_COMMENT | Comment | MySQL extension |
Notes:
In SHOW
, the Type
display includes values from several different
COLUMNS
columns.
ORDINAL_POSITION
is necessary because you
might want to say ORDER BY
ORDINAL_POSITION
. Unlike SHOW
,
SELECT
does not have automatic ordering.
CHARACTER_OCTET_LENGTH
should be the same
as CHARACTER_MAXIMUM_LENGTH
, except for
multi-byte character sets.
CHARACTER_SET_NAME
can be derived from
Collation
. For example, if you say
SHOW FULL COLUMNS FROM t
, and you see in
the Collation
column a value of
latin1_swedish_ci
, the character set is
what's before the first underscore: latin1
.
The following statements are nearly equivalent:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name
' [AND table_schema = 'db_name
'] [AND column_name LIKE 'wild
'] SHOW COLUMNS FROMtbl_name
[FROMdb_name
] [LIKE 'wild
']
The STATISTICS
table provides information about
table indexes.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
TABLE_CATALOG | NULL | |
TABLE_SCHEMA | = Database | |
TABLE_NAME | Table | |
NON_UNIQUE | Non_unique | |
INDEX_SCHEMA | = Database | |
INDEX_NAME | Key_name | |
SEQ_IN_INDEX | Seq_in_index | |
COLUMN_NAME | Column_name | |
COLLATION | Collation | |
CARDINALITY | Cardinality | |
SUB_PART | Sub_part | MySQL extension |
PACKED | Packed | MySQL extension |
NULLABLE | Null | MySQL extension |
INDEX_TYPE | Index_type | MySQL extension |
COMMENT | Comment | MySQL extension |
Notes:
There is no standard table for indexes. The preceding list is
similar to what SQL Server 2000 returns for
sp_statistics
, except that we replaced the
name QUALIFIER
with
CATALOG
and we replaced the name
OWNER
with SCHEMA
.
Clearly, the preceding table and the output from SHOW
INDEX
are derived from the same parent. So the
correlation is already close.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name
' [AND table_schema = 'db_name
'] SHOW INDEX FROMtbl_name
[FROMdb_name
]
The USER_PRIVILEGES
table provides information
about global privileges. This information comes from the
mysql.user
grant table.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
GRANTEE | '
value, MySQL extension | |
TABLE_CATALOG | NULL , MySQL extension | |
PRIVILEGE_TYPE | MySQL extension | |
IS_GRANTABLE | MySQL extension |
Notes:
This is a non-standard table. It takes its values from the
mysql.user
table.
The SCHEMA_PRIVILEGES
table provides
information about schema (database) privileges. This information
comes from the mysql.db
grant table.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
GRANTEE | '
value, MySQL extension | |
TABLE_CATALOG | NULL , MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
PRIVILEGE_TYPE | MySQL extension | |
IS_GRANTABLE | MySQL extension |
Notes:
This is a non-standard table. It takes its values from the
mysql.db
table.
The TABLE_PRIVILEGES
table provides information
about table privileges. This information comes from the
mysql.tables_priv
grant table.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
GRANTEE | '
value | |
TABLE_CATALOG | NULL | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
PRIVILEGE_TYPE
can contain one (and only
one) of these values: SELECT
,
INSERT
, UPDATE
,
REFERENCES
, ALTER
,
INDEX
, DROP
,
CREATE VIEW
.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES SHOW GRANTS ...
The COLUMN_PRIVILEGES
table provides
information about column privileges. This information comes from
the mysql.columns_priv
grant table.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
GRANTEE | '
value | |
TABLE_CATALOG | NULL | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
In the output from SHOW FULL COLUMNS
, the
privileges are all in one field and in lowercase, for example,
select,insert,update,references
. In
COLUMN_PRIVILEGES
, there is one privilege
per row, in uppercase.
PRIVILEGE_TYPE
can contain one (and only
one) of these values: SELECT
,
INSERT
, UPDATE
,
REFERENCES
.
If the user has GRANT OPTION
privilege,
IS_GRANTABLE
should be
YES
. Otherwise,
IS_GRANTABLE
should be
NO
. The output does not list GRANT
OPTION
as a separate privilege.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES SHOW GRANTS ...
The CHARACTER_SETS
table provides information
about available character sets.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
CHARACTER_SET_NAME | Charset | |
DEFAULT_COLLATE_NAME | Default collation | |
DESCRIPION | Description | MySQL extension |
MAXLEN | Maxlen | MySQL extension |
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS [WHERE name LIKE 'wild
'] SHOW CHARACTER SET [LIKE 'wild
']
The COLLATIONS
table provides information about
collations for each character set.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
COLLATION_NAME | Collation | |
CHARACTER_SET_NAME | Charset | MySQL extension |
ID | Id | MySQL extension |
IS_DEFAULT | Default | MySQL extension |
IS_COMPILED | Compiled | MySQL extension |
SORTLEN | Sortlen | MySQL extension |
The following statements are equivalent:
SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS [WHERE collation_name LIKE 'wild
'] SHOW COLLATION [LIKE 'wild
']
The COLLATION_CHARACTER_SET_APPLICABILITY
table
indicates what character set is applicable for what collation. The
columns are equivalent to the first two display fields that we get
from SHOW COLLATION
.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
COLLATION_NAME | Collation | |
CHARACTER_SET_NAME | Charset |
The TABLE_CONSTRAINTS
table describes which
tables have constraints.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
CONSTRAINT_CATALOG | NULL | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_SCHEMA | ||
TABLE_NAME | ||
CONSTRAINT_TYPE |
Notes:
The CONSTRAINT_TYPE
value can be
UNIQUE
, PRIMARY KEY
, or
FOREIGN KEY
.
The UNIQUE
and PRIMARY
KEY
information is about the same as what you get
from the Key_name
field in the output from
SHOW INDEX
when the
Non_unique
field is 0
.
The CONSTRAINT_TYPE
column can contain one
of these values: UNIQUE
, PRIMARY
KEY
, FOREIGN KEY
,
CHECK
. This is a CHAR
(not ENUM
) column. The
CHECK
value is not available until we
support CHECK
.
The KEY_COLUMN_USAGE
table describes which key
columns have constraints.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
CONSTRAINT_CATALOG | NULL | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_CATALOG | ||
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
ORDINAL_POSITION | ||
POSITION_IN_UNIQUE_CONSTRAINT | ||
REFERENCED_TABLE_SCHEMA | ||
REFERENCED_TABLE_NAME | ||
REFERENCED_COLUMN_NAME |
Notes:
If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.
The value of ORDINAL_POSITION
is the
column's position within the constraint, not the column's
position within the table. Column positions are numbered
beginning with 1.
The value of POSITION_IN_UNIQUE_CONSTRAINT
is NULL
for unique and primary-key
constraints. For foreign-key constraints, it is the ordinal
position in key of the table that is being referenced.
For example, suppose that there are two tables name
t1
and t3
that have the
following definitions:
CREATE TABLE t1 ( s1 INT, s2 INT, s3 INT, PRIMARY KEY(s3) ) ENGINE=InnoDB; CREATE TABLE t3 ( s1 INT, s2 INT, s3 INT, KEY(s1), CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3) ) ENGINE=InnoDB;
For those two tables, the KEY_COLUMN_USAGE
table has two rows:
One row with CONSTRAINT_NAME
=
'PRIMARY'
,
TABLE_NAME
= 't1'
,
COLUMN_NAME
= 's3'
,
ORDINAL_POSITION
=
1
,
POSITION_IN_UNIQUE_CONSTRAINT
=
NULL
.
One row with CONSTRAINT_NAME
=
'CO'
, TABLE_NAME
=
't3'
, COLUMN_NAME
=
's2'
,
ORDINAL_POSITION
=
1
,
POSITION_IN_UNIQUE_CONSTRAINT
=
1
.
REFERENCED_TABLE_SCHEMA
,
REFERENCED_TABLE_NAME
, and
REFERENCED_COLUMN_NAME
were added in MySQL
5.0.6.
The ROUTINES
table provides information about
stored routines (both procedures and functions). The
ROUTINES
table does not include user-defined
functions (UDFs) at this time.
The column named “mysql.proc
name”
indicates the mysql.proc
table column that
corresponds to the INFORMATION_SCHEMA.ROUTINES
table column, if any.
INFORMATION_SCHEMA
Name | mysql.proc Name | Remarks |
SPECIFIC_NAME | specific_name | |
ROUTINE_CATALOG | NULL | |
ROUTINE_SCHEMA | db | |
ROUTINE_NAME | name | |
ROUTINE_TYPE | type | {PROCEDURE|FUNCTION} |
DTD_IDENTIFIER | (data type descriptor) | |
ROUTINE_BODY | SQL | |
ROUTINE_DEFINITION | body | |
EXTERNAL_NAME | NULL | |
EXTERNAL_LANGUAGE | language | NULL |
PARAMETER_STYLE | SQL | |
IS_DETERMINISTIC | is_deterministic | |
SQL_DATA_ACCESS | sql_data_access | |
SQL_PATH | NULL | |
SECURITY_TYPE | security_type | |
CREATED | created | |
LAST_ALTERED | modified | |
SQL_MODE | sql_mode | MySQL extension |
ROUTINE_COMMENT | comment | MySQL extension |
DEFINER | definer | MySQL extension |
Notes:
MySQL calculates EXTERNAL_LANGUAGE
thus:
If mysql.proc.language='SQL'
,
EXTERNAL_LANGUAGE
is
NULL
Otherwise, EXTERNAL_LANGUAGE
is what is
in mysql.proc.language
. However, we do
not have external languages yet, so it is always
NULL
.
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 | NULL | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
VIEW_DEFINITION | ||
CHECK_OPTION | ||
IS_UPDATABLE | ||
DEFINER | ||
SECURITY_TYPE |
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 always has a value
of NONE
.
The IS_UPDATABLE
column is
YES
if the view is updatable,
NO
if the view is not updatable.
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
.
The TRIGGERS
table provides information about
triggers. You must have the SUPER
privilege to
access this table.
INFORMATION_SCHEMA
Name | SHOW Name | Remarks |
TRIGGER_CATALOG | NULL | |
TRIGGER_SCHEMA | ||
TRIGGER_NAME | Trigger | |
EVENT_MANIPULATION | Event | |
EVENT_OBJECT_CATALOG | NULL | |
EVENT_OBJECT_SCHEMA | ||
EVENT_OBJECT_TABLE | Table | |
ACTION_ORDER | 0 | |
ACTION_CONDITION | NULL | |
ACTION_STATEMENT | Statement | |
ACTION_ORIENTATION | ROW | |
ACTION_TIMING | Timing | |
ACTION_REFERENCE_OLD_TABLE | NULL | |
ACTION_REFERENCE_NEW_TABLE | NULL | |
ACTION_REFERENCE_OLD_ROW | OLD | |
ACTION_REFERENCE_NEW_ROW | NEW | |
CREATED | NULL (0 ) | |
SQL_MODE | MySQL extension | |
DEFINER | MySQL extension |
Notes:
The TRIGGERS
table was added in MySQL
5.0.10.
The TRIGGER_SCHEMA
and
TRIGGER_NAME
columns contain the name of
the database in which the trigger occurs and the trigger name,
respectively.
The EVENT_MANIPULATION
column contains one
of the values 'INSERT'
,
'DELETE'
, or 'UPDATE'
.
As noted in Chapter 18, Triggers, every trigger is
associated with exactly one table. The
EVENT_OBJECT_SCHEMA
and
EVENT_OBJECT_TABLE
columns contain the
database in which this table occurs, and the table's name.
The ACTION_ORDER
statement contains the
ordinal position of the trigger's action within the list of
all similar triggers on the same table. Currently, this value
is always 0
, because it is not possible to
have more than one trigger with the same
EVENT_MANIPULATION
and
ACTION_TIMING
on the same table.
The ACTION_STATEMENT
column contains the
statement to be executed when the trigger is invoked. This is
the same as the text displayed in the
Statement
column of the output from
SHOW TRIGGERS
. Note that this text uses
UTF-8 encoding.
The ACTION_ORIENTATION
column always
contains the value 'ROW'
.
The ACTION_TIMING
column contains one of
the two values 'BEFORE'
or
'AFTER'
.
The columns ACTION_REFERENCE_OLD_ROW
and
ACTION_REFERENCE_NEW_ROW
contain the old
and new column identifiers, respectively. This means that
ACTION_REFERENCE_OLD_ROW
always contains
the value 'OLD'
and
ACTION_REFERENCE_NEW_ROW
always contains
the value 'NEW'
.
The SQL_MODE
column shows the server SQL
mode that was in effect at the time when the trigger was
created (and thus which remains in effect for this trigger
whenever it is invoked, regardless of the current
server SQL mode). The possible range of values for
this column is the same as that of the
sql_mode
system variable. See
Section 5.2.5, “The Server SQL Mode”.
The DEFINER
column was added in MySQL
5.0.17. DEFINER
indicates who defined the
trigger.
The following columns currently always contain
NULL
: TRIGGER_CATALOG
,
EVENT_OBJECT_CATALOG
,
ACTION_CONDITION
,
ACTION_REFERENCE_OLD_TABLE
,
ACTION_REFERENCE_NEW_TABLE
, and
CREATED
.
Example, using the ins_sum
trigger defined in
Section 18.3, “Using Triggers”:
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER: me@localhost
We intend to implement additional
INFORMATION_SCHEMA
tables. In particular, we
acknowledge the need for the PARAMETERS
and
REFERENTIAL_CONSTRAINTS
tables.
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;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
16 rows in set (0.00 sec)
SHOW COLUMNS
and DESCRIBE
can display information about the columns in individual
INFORMATION_SCHEMA
tables.
Several SHOW
statement have been extended to
allow a WHERE
clause:
SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW DATABASES SHOW FUNCTION STATUS SHOW KEYS SHOW OPEN TABLES SHOW PROCEDURE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW VARIABLES
The WHERE
clause, if present, is evaluated
against the column names displayed by the SHOW
statement. For example, the SHOW CHARACTER SET
statement produces these output columns:
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
...
To use a WHERE
clause with SHOW
CHARACTER SET
, you would refer to those column names. As
an example, the following statement displays information about
character sets for which the default collation contains the string
'japanese'
:
mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+
This statement displays the multi-byte character sets:
mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+