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 12.4.5, “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
. See Chapter 19, INFORMATION_SCHEMA
Tables.
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 noncharacter 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.
User Comments
Add your own comment.