Chapter 19. INFORMATION_SCHEMA Tables
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.
The definition for character columns (for example,
TABLES.TABLE_NAME) is generally
VARCHAR(N) CHARACTER SET
utf8 where N is at least 64.
MySQL uses the default collation for this character set
(utf8_general_ci) for all searches, sorts,
comparisons, and other string operations on such columns. Values
such as table names in INFORMATION_SCHEMA columns
are treated as strings, not identifiers, and are not compared using
the identifier rules described in
Section 8.2.2, “Identifier Case Sensitivity”. If the result of a
string operation on an INFORMATION_SCHEMA column
differs from expectations, a workaround is to use an explicit
COLLATE clause to force a suitable collation
(Section 9.1.7.1, “Using COLLATE in SQL Statements”). You can also use the
UPPER() or
LOWER() function. For example, in a
WHERE clause, you might use:
WHERE TABLE_NAME COLLATE utf8_bin = 'City'
WHERE TABLE_NAME COLLATE utf8_general_ci = 'city'
WHERE UPPER(TABLE_NAME) = 'CITY'
WHERE LOWER(TABLE_NAME) = 'city'
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. In some cases (for example,
the ROUTINE_DEFINITION column in the
INFORMATION_SCHEMA.ROUTINES table),
users who have insufficient privileges will see
NULL.
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 19.19, “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://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5.
Each section indicates what SHOW
statement is equivalent to a SELECT
that retrieves information from
INFORMATION_SCHEMA, if there is such a statement.
For SHOW statements that display
information for the default database if you omit a FROM
db_name clause, you can often
select information for the default database by adding an
AND TABLE_SCHEMA = CURRENT_DATABASE() condition
to the WHERE clause of a query that retrieves
information from an INFORMATION_SCHEMA table.
Note
At present, there are some missing columns and some columns out of
order. We are working on this and updating the documentation as
changes are made.
For answers to questions that are often asked concerning the
INFORMATION_SCHEMA database, see
Section A.7, “MySQL 5.0 FAQ — INFORMATION_SCHEMA”.
19.1. The INFORMATION_SCHEMA SCHEMATA Table
A schema is a database, so the
SCHEMATA table provides information
about databases.
Notes:
The following statements are equivalent:
SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA
[WHERE SCHEMA_NAME LIKE 'wild']
SHOW DATABASES
[LIKE 'wild']
19.2. The INFORMATION_SCHEMA TABLES Table
The TABLES table provides information
about tables in databases.
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. Currently, the
TABLES table does not list
TEMPORARY tables.
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'
[AND table_name LIKE 'wild']
SHOW TABLES
FROM db_name
[LIKE 'wild']
19.3. The INFORMATION_SCHEMA COLUMNS Table
The COLUMNS table provides
information about columns in tables.
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 is 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
FROM tbl_name
[FROM db_name]
[LIKE 'wild']
19.4. The INFORMATION_SCHEMA STATISTICS Table
The STATISTICS table provides
information about table indexes.
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
FROM tbl_name
FROM db_name
19.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table
The USER_PRIVILEGES table provides
information about global privileges. This information comes from
the mysql.user grant table.
Notes:
19.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
The SCHEMA_PRIVILEGES table provides
information about schema (database) privileges. This information
comes from the mysql.db grant table.
Notes:
19.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
The TABLE_PRIVILEGES table provides
information about table privileges. This information comes from
the mysql.tables_priv grant table.
Notes:
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SHOW GRANTS ...
19.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
The COLUMN_PRIVILEGES table provides
information about column privileges. This information comes from
the mysql.columns_priv grant table.
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 ...
19.9. The INFORMATION_SCHEMA CHARACTER_SETS Table
The CHARACTER_SETS table provides
information about available character sets.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
[WHERE name LIKE 'wild']
SHOW CHARACTER SET
[LIKE 'wild']
19.10. The INFORMATION_SCHEMA COLLATIONS Table
The COLLATIONS table provides
information about collations for each character set.
COLLATION_NAME is the collation name.
CHARACTER_SET_NAME is the name of the
character set with which the collation is associated.
ID is the collation ID.
IS_DEFAULT indicates whether the collation
is the default for its character set.
IS_COMPILED indicates whether the character
set is compiled into the server.
SORTLEN is related to the amount of memory
required to sort strings expressed in the character set.
Collation information is also available from the
SHOW COLLATION statement. The
following statements are equivalent:
SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
[WHERE collation_name LIKE 'wild']
SHOW COLLATION
[LIKE 'wild']
19.11. The 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.
19.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
The TABLE_CONSTRAINTS table describes
which tables have constraints.
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.
19.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
The KEY_COLUMN_USAGE table describes
which key columns have constraints.
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.
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.
19.14. The 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.
Notes:
19.15. The INFORMATION_SCHEMA VIEWS Table
The VIEWS table provides information
about views in databases. You must have the
SHOW VIEW privilege to access this
table.
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.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 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.
19.16. The INFORMATION_SCHEMA TRIGGERS Table
The TRIGGERS table provides
information about triggers. You must have the
SUPER privilege to access this
table.
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 Section 18.3, “Using 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.1.7, “Server SQL Modes”.
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
See also Section 12.5.5.35, “SHOW TRIGGERS Syntax”.
19.17. The INFORMATION_SCHEMA PROFILING Table
This section does not apply to MySQL Enterprise Server users.
The PROFILING table provides statement
profiling information. Its contents correspond to the information
produced by the SHOW PROFILES and
SHOW PROFILE statements (see
Section 12.5.5.29, “SHOW PROFILES Syntax”). The table is empty unless the
profiling session variable is set
to 1.
Notes:
The PROFILING table was added in MySQL
5.0.37.
QUERY_ID is a numeric statement identifier.
SEQ is a sequence number indicating the
display order for rows with the same
QUERY_ID value.
STATE is the profiling state to which the
row measurements apply.
DURATION indicates how long statement
execution remained in the given state, in seconds.
CPU_USER and CPU_SYSTEM
indicate user and system CPU use, in seconds.
CONTEXT_VOLUNTARY and
CONTEXT_INVOLUNTARY indicate how many
voluntary and involuntary context switches occurred.
BLOCK_OPS_IN and
BLOCK_OPS_OUT indicate the number of block
input and output operations.
MESSAGES_SENT and
MESSAGES_RECEIVED indicate the number of
communication messages sent and received.
PAGE_FAULTS_MAJOR and
PAGE_FAULTS_MINOR indicate the number of
major and minor page faults.
SWAPS indicates how many swaps occurred.
SOURCE_FUNCTION,
SOURCE_FILE, and
SOURCE_LINE provide information indicating
where in the source code the profiled state executes.
19.18. Other INFORMATION_SCHEMA Tables
We intend to implement additional
INFORMATION_SCHEMA tables. In particular, we
acknowledge the need for the PARAMETERS and
REFERENTIAL_CONSTRAINTS tables.
(REFERENTIAL_CONSTRAINTS is implemented in
MySQL 5.1, and PARAMETERS is implemented in
MySQL 5.6.)
19.19. Extensions to SHOW Statements
Some extensions to SHOW statements
accompany the implementation of
INFORMATION_SCHEMA:
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.
SHOW statements that accept a
LIKE clause to limit the rows
displayed have been extended to allow a WHERE
clause that enables specification of more general conditions that
selected rows must satisfy:
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW INDEX
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 |
+---------+---------------------------+---------------------+--------+
