table_charset
NAME
table_charset: list tables, their character sets and collationsTYPE
ViewDESCRIPTION
A table is associated with a character set and a collation. Surprisingly, INFORMATION_SCHEMA's TABLES table only lists a table's collation, and neglects to provide with the character set.
A character set is easily deduced by given collation, since a collation relates to a single character set.
table_charset provides this convenient connection.
STRUCTURE
mysql> DESC common_schema.table_charset; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | CHARACTER_SET_NAME | varchar(32) | NO | | | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | +--------------------+-------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- TABLE_SCHEMA: name of schema (database)
- TABLE_NAME: name of table
- CHARACTER_SET_NAME: table's defined character set
- TABLE_COLLATION: table's collation
EXAMPLES
mysql> SELECT * FROM common_schema.table_charset WHERE TABLE_SCHEMA IN ('sakila', 'world');
+--------------+-----------------+--------------------+-------------------+
| TABLE_SCHEMA | TABLE_NAME | CHARACTER_SET_NAME | TABLE_COLLATION |
+--------------+-----------------+--------------------+-------------------+
| world | City | latin1 | latin1_swedish_ci |
| world | Country | latin1 | latin1_swedish_ci |
| world | CountryLanguage | latin1 | latin1_swedish_ci |
| sakila | actor | utf8 | utf8_general_ci |
| sakila | address | utf8 | utf8_general_ci |
| sakila | category | utf8 | utf8_general_ci |
| sakila | city | utf8 | utf8_general_ci |
| sakila | country | utf8 | utf8_general_ci |
| sakila | customer | utf8 | utf8_general_ci |
| sakila | film | utf8 | utf8_general_ci |
| sakila | film_actor | utf8 | utf8_general_ci |
| sakila | film_category | utf8 | utf8_general_ci |
| sakila | film_text | utf8 | utf8_general_ci |
| sakila | inventory | utf8 | utf8_general_ci |
| sakila | language | utf8 | utf8_general_ci |
| sakila | payment | utf8 | utf8_general_ci |
| sakila | rental | utf8 | utf8_general_ci |
| sakila | staff | utf8 | utf8_general_ci |
| sakila | store | utf8 | utf8_general_ci |
+--------------+-----------------+--------------------+-------------------+