INFORMATION_SCHEMA
Information Database
INFORMATION_SCHEMA
support is available in MySQL 5.0.2 and later.
It 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'' or ``system catalog.''
Here is an example:
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 engine.
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 you won't actually see
any file associated with them.
Each MySQL user has the right to access these tables, but only the rows in the tables that correspond to objects for which the user has the proper access privileges.
Advantages of SELECT
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
:
SELECT
works, they only need to learn the object names.
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, there are enhancements to SHOW
in MySQL 5.0, too.
These are described in section 21.2 Extensions to SHOW
Statements.
Standards
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
.
In effect, we have a new ``database'' named information_schema
,
though there is never a need to make a file by that name. It is not
possible to issue USE INFORMATION_SCHEMA
. The only thing you can do
with these tables is SELECT
. You cannot update, insert, delete, or
even reference them.
Privileges
There is no difference between the current (SHOW
)
privilege requirement and the SELECT
requirement.
In either case, you have to have some privilege
on an object in order to see information about it.
INFORMATION_SCHEMA
Tables
SHOW
Statements
INFORMATION_SCHEMA
TablesExplanation of following sections
In the following sections, we take the tables
and columns that are in INFORMATION_SCHEMA
.
For each column, there are three pieces of information:
SHOW
name'' indicates what the equivalent
field name is in the closest SHOW
statement, if any.
To avoid using any name that's reserved in the standard or in DB2 or in SQL
Server or in Oracle we changed the names of 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(N) CHARACTER SET
utf8
where N is at least 64.
Each section indicates what SHOW
statement is equivalent to a
SELECT
that retrieves information from INFORMATION_SCHEMA
, or
else that there is no such equivalent statement.
Note: At present, there are some missing columns and some columns out of order. We are working on this and intend to update the documentation as changes are made.
INFORMATION_SCHEMA SCHEMATA
Table
INFORMATION_SCHEMA TABLES
Table
INFORMATION_SCHEMA COLUMNS
Table
INFORMATION_SCHEMA STATISTICS
Table
INFORMATION_SCHEMA USER_PRIVILEGES
Table
INFORMATION_SCHEMA SCHEMA_PRIVILEGES
Table
INFORMATION_SCHEMA TABLE_PRIVILEGES
Table
INFORMATION_SCHEMA COLUMN_PRIVILEGES
Table
INFORMATION_SCHEMA CHARACTER_SETS
Table
INFORMATION_SCHEMA COLLATIONS
Table
INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY
Table
INFORMATION_SCHEMA TABLE_CONSTRAINTS
Table
INFORMATION_SCHEMA KEY_COLUMN_USAGE
Table
INFORMATION_SCHEMA ROUTINES
Table
INFORMATION_SCHEMA VIEWS
Table
INFORMATION_SCHEMA
Tables
INFORMATION_SCHEMA SCHEMATA
Table
A schema is a database, so the SCHEMATA
table provides information
about databases.
Standard Name | SHOW name | Remarks |
CATALOG_NAME | - | NULL
|
SCHEMA_NAME | Database | |
SCHEMA_OWNER | omit | |
DEFAULT_CHARACTER_SET_CATALOG | omit | |
DEFAULT_CHARACTER_SET_SCHEMA | omit | |
DEFAULT_CHARACTER_SET_NAME | ||
SQL_PATH | NULL
|
Notes:
SQL_PATH
, we might eventually support
something in MySQL 5.x. For now, it's always NULL
.
The following statements are equivalent:
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA [WHERE SCHEMA_NAME LIKE 'wild'] SHOW DATABASES [LIKE 'wild']
INFORMATION_SCHEMA TABLES
Table
The TABLES
table provides information about tables in databases.
Standard Name | SHOW name | Remarks |
TABLE_CATALOG | NULL
| |
TABLE_SCHEMA | Table_ ... | |
TABLE_NAME | Table_ ... | |
TABLE_TYPE | ||
SELF_REFERENCING_COLUMN_NAME | omit | |
REFERENCE_GENERATION | omit | |
USER_DEFINED_TYPE_NAME | omit | |
IS_INSERTABLE_INTO | omit | |
IS_TYPED | omit | |
COMMIT_ACTION | omit | |
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.)
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 [FROM db_name] [LIKE 'wild']
INFORMATION_SCHEMA COLUMNS
Table
The COLUMNS
table provides information about columns in tables.
Standard 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_PRECISION_RADIX | omit | |
NUMERIC_SCALE | Type | |
DATETIME_PRECISION | omit | |
INTERVAL_TYPE | omit | |
INTERVAL_PRECISION | omit | |
CHARACTER_SET_CATALOG | omit | |
CHARACTER_SET_SCHEMA | omit | |
CHARACTER_SET_NAME | ||
COLLATION_CATALOG | omit | |
COLLATION_SCHEMA | omit | |
COLLATION_NAME | Collation | |
DOMAIN_NAME | omit | |
UDT_CATALOG | omit | |
UDT_SCHEMA | omit | |
UDT_NAME | omit | |
SCOPE_CATALOG | omit | |
SCOPE_SCHEMA | omit | |
SCOPE_NAME | omit | |
MAXIMUM_CARDINALITY | omit | |
DTD_IDENTIFIER | omit | |
IS_SELF_REFERENCING | omit | |
IS_IDENTITY | omit | |
IDENTITY_GENERATION | omit | |
IDENTITY_START | omit | |
IDENTITY_INCREMENT | omit | |
IDENTITY_MAXIMUM | omit | |
IDENTITY_MINIMUM | omit | |
IDENTITY_CYCLE | omit | |
IS_GENERATED | omit | |
GENERATION_EXPRESSION | omit | |
COLUMN_KEY | Key | MySQL extension |
EXTRA | Extra | MySQL extension |
COLUMN_COMMENT | Comment | MySQL extension |
Notes:
SHOW
, the Type
display includes values from several
different COLUMNS
columns.
ORDINAL_POSITION
is necessary because you might someday 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 schema_name = 'db_name'] [AND column_name LIKE 'wild'] SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
INFORMATION_SCHEMA STATISTICS
Table
The STATISTICS
table provides information about table indexes.
Standard Name | SHOW name | Remarks |
TABLE_CATALOG | NULL
| |
TABLE_SCHEMA | = Database | |
TABLE_NAME | Table | |
NON_UNIQUE | Non_unique | |
INDEX_SCHEMA | = Database | |
INDEX_NAME | Key_name | |
TYPE | omit | |
SEQ_IN_INDEX | Seq_in_index | |
COLUMN_NAME | Column_name | |
COLLATION | Collation | |
CARDINALITY | Cardinality | |
PAGES | omit | |
FILTER_CONDITION | omit | |
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:
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 schema_name = 'db_name' SHOW INDEX FROM tbl_name [FROM db_name]
INFORMATION_SCHEMA USER_PRIVILEGES
Table
The USER_PRIVILEGES
table provides information about global
privileges. This information comes from the mysql.user
grant table.
Standard Name | SHOW name | Remarks |
GRANTEE | e.g. 'user'@'host' | |
TABLE_CATALOG | NULL
| |
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
mysql.user
table.
INFORMATION_SCHEMA SCHEMA_PRIVILEGES
Table
The SCHEMA_PRIVILEGES
table provides information about schema
(database) privileges. This information comes from the mysql.db
grant table.
Standard Name | SHOW name | Remarks |
GRANTEE | e.g. 'user'@'host' | |
TABLE_CATALOG | NULL
| |
TABLE_SCHEMA | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
mysql.db
table.
INFORMATION_SCHEMA TABLE_PRIVILEGES
Table
The TABLE_PRIVILEGES
table provides information about table
privileges. This information comes from the mysql.tables_priv
grant table.
Standard Name | SHOW name | Remarks |
GRANTOR | omit | |
GRANTEE | e.g. 'user'@'host' | |
TABLE_CATALOG | NULL
| |
TABLE_SCHEMA | ||
TABLE_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE | ||
WITH_HIERARCHY | omit |
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES SHOW GRANTS ...
PRIVILEGE_TYPE
can contain one (and only one) of these values:
SELECT
, INSERT
, UPDATE
, REFERENCES
,
ALTER
, INDEX
, DROP
, CREATE VIEW
.
INFORMATION_SCHEMA COLUMN_PRIVILEGES
Table
The COLUMN_PRIVILEGES
table provides information about column
privileges. This information comes from the mysql.columns_priv
grant table.
Standard Name | SHOW name | Remarks |
GRANTOR | omit | |
GRANTEE | e.g. 'user'@'host' | |
TABLE_CATALOG | NULL
| |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
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 row per privilege,
and it's uppercase.
PRIVILEGE_TYPE
can contain one (and only one) of these values:
SELECT
, INSERT
, UPDATE
, REFERENCES
.
GRANT OPTION
privilege, then 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 ...
INFORMATION_SCHEMA CHARACTER_SETS
Table
The CHARACTER_SETS
table provides information about available
character sets.
Standard Name | SHOW name | Remarks |
CHARACTER_SET_CATALOG | omit | |
CHARACTER_SET_SCHEMA | omit | |
CHARACTER_SET_NAME | Charset | |
CHARACTER_REPERTOIRE | omit | |
FORM_OF_USE | omit | |
NUMBER_OF_CHARACTERS | omit | |
DEFAULT_COLLATE_CATALOG | omit | |
DEFAULT_COLLATE_SCHEMA | omit | |
DEFAULT_COLLATE_NAME | Default collation | |
DESCRIPION | Description | MySQL extension |
MAXLEN | Maxlen | MySQL extension |
Notes:
Description
and Maxlen
columns in the output from SHOW
CHARACTER SET
.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS [WHERE name LIKE 'wild'] SHOW CHARACTER SET [LIKE 'wild']
INFORMATION_SCHEMA COLLATIONS
Table
The COLLATIONS
table provides information about collations for each
character set.
Standard Name | SHOW name | Remarks |
COLLATION_CATALOG | omit | |
COLLATION_SCHEMA | omit | |
COLLATION_NAME | Collation | |
PAD_ATTRIBUTE | omit | |
COLLATION_TYPE | omit | |
COLLATION_DEFINITION | omit | |
COLLATION_DICTIONARY | omit | |
CHARACTER_SET_NAME | omit MySQL extension | |
ID | omit MySQL extension | |
IS_DEFAULT | omit MySQL extension | |
IS_COMPILED | omit MySQL extension | |
SORTLEN | omit MySQL extension |
Notes:
Charset
,
Id
, Default
, Compiled
, and Sortlen
columns in
the output from SHOW COLLATION
.
The following statements are equivalent:
SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS [WHERE collation_name LIKE 'wild'] SHOW COLLATION [LIKE 'wild']
INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY
Table
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
.
Standard Name | SHOW name | Remarks |
COLLATION_CATALOG | omit | |
COLLATION_SCHEMA | omit | |
COLLATION_NAME | Collation | |
CHARACTER_SET_CATALOG | omit | |
CHARACTER_SET_SCHEMA | omit | |
CHARACTER_SET_NAME | Charset |
INFORMATION_SCHEMA TABLE_CONSTRAINTS
Table
The TABLE_CONSTRAINTS
table describes which tables have
constraints.
Standard Name | SHOW name | Remarks |
CONSTRAINT_CATALOG | NULL
| |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_CATALOG | omit | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
CONSTRAINT_TYPE | ||
IS_DEFERRABLE | omit | |
INITIALLY_DEFERRED | omit |
Notes:
CONSTRAINT_TYPE
value can be UNIQUE
, PRIMARY KEY
,
or FOREIGN KEY
.
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
.
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
.
INFORMATION_SCHEMA KEY_COLUMN_USAGE
Table
The KEY_COLUMN_USAGE
table describes which key columns have
constraints.
Standard 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 |
Notes:
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.
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:
CONSTRAINT_NAME
='PRIMARY',
TABLE_NAME
='t1', COLUMN_NAME
='s3', ORDINAL_POSITION
=1,
POSITION_IN_UNIQUE_CONSTRAINT
=NULL.
CONSTRAINT_NAME
='CO',
TABLE_NAME
='t3', COLUMN_NAME
='s2', ORDINAL_POSITION
=1,
POSITION_IN_UNIQUE_CONSTRAINT
=1.
INFORMATION_SCHEMA ROUTINES
Table
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.
Standard Name | mysql.proc name | Remarks |
SPECIFIC_CATALOG | omit | |
SPECIFIC_SCHEMA | db | omit |
SPECIFIC_NAME | specific_name | |
ROUTINE_CATALOG | NULL
| |
ROUTINE_SCHEMA | db | |
ROUTINE_NAME | name | |
MODULE_CATALOG | omit | |
MODULE_SCHEMA | omit | |
MODULE_NAME | omit | |
USER_DEFINED_TYPE_CATALOG | omit | |
USER_DEFINED_TYPE_SCHEMA | omit | |
USER_DEFINED_TYPE_NAME | omit | |
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 | |
IS_NULL_CALL | omit | |
SQL_PATH | NULL
| |
SCHEMA_LEVEL_ROUTINE | omit | |
MAX_DYNAMIC_RESULT_SETS | omit | |
IS_USER_DEFINED_CAST | omit | |
IS_IMPLICITLY_INVOCABLE | omit | |
SECURITY_TYPE | security_type | |
TO_SQL_SPECIFIC_CATALOG | omit | |
TO_SQL_SPECIFIC_SCHEMA | omit | |
TO_SQL_SPECIFIC_NAME | omit | |
AS_LOCATOR | omit | |
CREATED | created | |
LAST_ALTERED | modified | |
NEW_SAVEPOINT_LEVEL | omit | |
IS_UDT_DEPENDENT | omit | |
RESULT_CAST_FROM_DTD_IDENTIFIER | omit | |
RESULT_CAST_AS_LOCATOR | omit | |
SQL_MODE | sql_mode | MySQL extension |
ROUTINE_COMMENT | comment | MySQL extension |
DEFINER | definer | MySQL extension |
Notes:
EXTERNAL_LANGUAGE
thus:
mysql.proc.language='SQL'
, then EXTERNAL_LANGUAGE
is
NULL
EXTERNAL_LANGUAGE
is what's in
mysql.proc.language
. However, we don't have external languages yet,
so it's always NULL
.
INFORMATION_SCHEMA VIEWS
Table
The VIEWS
table provides information about views in databases.
Standard Name | SHOW name | Remarks |
TABLE_CATALOG | NULL
| |
TABLE_SCHEMA | ||
TABLE_NAME | ||
VIEW_DEFINITION | ||
CHECK_OPTION | ||
IS_UPDATABLE | ||
INSERTABLE_INTO | omit |
Notes:
SHOW VIEW
, without which
you cannot see the VIEWS
table.
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
. For
example, if 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 is:
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
CHECK_OPTION
column always has a value of NONE
.
IS_UPDATABLE
column is YES
if the view is updatable,
NO
if the view is not updatable.
INFORMATION_SCHEMA
Tables
We will add more INFORMATION_SCHEMA
tables soon. Particularly, we
acknowledge the need for INFORMATION_SCHEMA.PARAMETERS
and for
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
and for
INFORMATION_SCHEMA.TRIGGERS
.
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.
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 | +---------------------------------------+ | SCHEMATA | | TABLES | | COLUMNS | | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | ROUTINES | | STATISTICS | | VIEWS | | USER_PRIVILEGES | | SCHEMA_PRIVILEGES | | TABLE_PRIVILEGES | | COLUMN_PRIVILEGES | | TABLE_CONSTRAINTS | | KEY_COLUMN_USAGE | +---------------------------------------+
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
COLLATION
statement produces these output columns:
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 | ISO 8859-1 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 | +---------+---------------------------+---------------------+--------+
Go to the first, previous, next, last section, table of contents.