Getting MySQL to display the encoding used for a specific column

Question

The MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/charset-syntax.html) says:

There are standard settings for character sets and collaborations at four levels: server, database, table and column. The description in the following sections may seem complicated, but in practice it has been found that multi-level defaulting leads to natural and obvious results.

I would like to interrogate a specific column CHAR / VARCHAR / TEXT and find out what MySQL encoding is. Is there an easy way to do this? I know that I can use SHOW CREATE TABLE <table> to see the default encoding for the table, but I would like to do the same at the column level as the docs suggest that this may not be the same as the table by by default.

Answer 1

You can do this in the information_schema.COLUMNS table.

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

Example output from the Gallery2 database:

 +-------------------------+--------------------------+--------------------+--------------+-----------------+ | 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 |