Home › MySQL show charset for table

MySQL show charset for table 

Dr. Xi
Joined:
04/09/2007
Posts:
727

March 05, 2011 14:39:42    Last update: March 05, 2011 14:40:35
Select from information_schema: 
mysql> select cc.character_set_name
    -> from information_schema.tables t,
    -> information_schema.collation_character_set_applicability cc
    -> where t.table_collation = cc.collation_name
    -> and t.table_name = 'host';
+--------------------+
| character_set_name |
+--------------------+
| utf8               |
+--------------------+
1 row in set (0.01 sec)

mysql>


Other columns available from the information_schema tables: 
mysql> desc tables;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)  | NO   |     |         |       |
| ENGINE          | varchar(64)  | YES  |     | NULL    |       |
| VERSION         | bigint(21)   | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)  | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21)   | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21)   | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21)   | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21)   | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21)   | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21)   | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21)   | YES  |     | NULL    |       |
| CREATE_TIME     | datetime     | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime     | YES  |     | NULL    |       |
| CHECK_TIME      | datetime     | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(64)  | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21)   | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255) | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(80)  | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
21 rows in set (0.01 sec)

mysql> desc collation_character_set_applicability;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME     | varchar(64) | NO   |     |         |       |
| CHARACTER_SET_NAME | varchar(64) | NO   |     |         |       |
+--------------------+-------------+------+-----+---------+-------+


Note that show create table <table_name> also works.
Share |
| Comment  | Tags
 
Easy email testing with http://www.ximailstop.com

Related Notes