10.6.3. SHOW Statements and INFORMATION_SCHEMA
Several SHOW statements provide additional
character set information. These include SHOW CHARACTER
SET, SHOW COLLATION, SHOW
CREATE DATABASE, SHOW CREATE TABLE
and SHOW COLUMNS. These statements are
described here briefly. For more information, see
Section 13.5.4, “SHOW Syntax”.
INFORMATION_SCHEMA has several tables that
contain information similar to that displayed by the
SHOW statements. For example, the
CHARACTER_SETS and
COLLATIONS tables contain the information
displayed by SHOW CHARACTER SET and
SHOW COLLATION.
Chapter 23, The INFORMATION_SCHEMA Database.
The SHOW CHARACTER SET command shows all
available character sets. It takes an optional
LIKE clause that indicates which character
set names to match. For example:
mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
+---------+-----------------------------+-------------------+--------+
The output from SHOW COLLATION includes all
available character sets. It takes an optional
LIKE clause that indicates which collation
names to match. For example:
mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1 | 5 | | | 0 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 |
| latin1_danish_ci | latin1 | 15 | | | 0 |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 |
| latin1_bin | latin1 | 47 | | Yes | 0 |
| latin1_general_ci | latin1 | 48 | | | 0 |
| latin1_general_cs | latin1 | 49 | | | 0 |
| latin1_spanish_ci | latin1 | 94 | | | 0 |
+-------------------+---------+----+---------+----------+---------+
SHOW CREATE DATABASE displays the
CREATE DATABASE statement that creates a
given database:
mysql> SHOW CREATE DATABASE test;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
If no COLLATE clause is shown, the default
collation for the character set applies.
SHOW CREATE TABLE is similar, but displays
the CREATE TABLE statement to create a given
table. The column definitions indicate any character set
specifications, and the table options include character set
information.
The SHOW COLUMNS statement displays the
collations of a table's columns when invoked as SHOW
FULL COLUMNS. Columns with CHAR,
VARCHAR, or TEXT data
types have collations. Numeric and other non-character types
have no collation (indicated by NULL as the
Collation value). For example:
mysql> SHOW FULL COLUMNS FROM person\G
*************************** 1. row ***************************
Field: id
Type: smallint(5) unsigned
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
Privileges: select,insert,update,references
Comment:
*************************** 2. row ***************************
Field: name
Type: char(60)
Collation: latin1_swedish_ci
Null: NO
Key:
Default:
Extra:
Privileges: select,insert,update,references
Comment:
The character set is not part of the display but is implied by
the collation name.
