mysql - 让 MySQL 显示用于特定列的编码

Question

MySQL 手册 (http://dev.mysql.com/doc/refman/5.1/en/charset-syntax.html) 说:

There are default settings for character sets and collations at four levels: server, database, table, and column. The description in the following sections may appear complex, but it has been found in practice that multiple-level defaulting leads to natural and obvious results.

我想查询一个特定的 CHAR/VARCHAR/TEXT 列并找出 MySQL 认为它是什么编码。是否有捷径可寻？我知道我可以使用 SHOW CREATE TABLE <table>查看表的默认字符集，但我想在列级别做同样的事情，因为文档建议它可能与表默认值不同。

Answer 1

您可以在 information_schema.COLUMNS 表中执行此操作。

SELECT 
  COLUMN_NAME,
  TABLE_NAME,
  CHARACTER_SET_NAME,
  COLUMN_TYPE,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'

Gallery2 数据库的示例输出:

+-------------------------+--------------------------+--------------------+--------------+-----------------+
| COLUMN_NAME             | TABLE_NAME               | CHARACTER_SET_NAME | COLUMN_TYPE  | COLLATION_NAME  |
+-------------------------+--------------------------+--------------------+--------------+-----------------+
| g_accessListId          | g2_AccessMap             | NULL               | int(11)      | NULL            |
| g_userOrGroupId         | g2_AccessMap             | NULL               | int(11)      | NULL            |
| g_permission            | g2_AccessMap             | NULL               | int(11)      | NULL            |
| g_itemId                | g2_AccessSubscriberMap   | NULL               | int(11)      | NULL            |
| g_accessListId          | g2_AccessSubscriberMap   | NULL               | int(11)      | NULL            |
| g_id                    | g2_AlbumItem             | NULL               | int(11)      | NULL            |
| g_theme                 | g2_AlbumItem             | utf8               | varchar(32)  | utf8_general_ci |
| g_orderBy               | g2_AlbumItem             | utf8               | varchar(128) | utf8_general_ci |
| g_orderDirection        | g2_AlbumItem             | utf8               | varchar(32)  | utf8_general_ci |