Discover collation of a MySQL column

Question

I previously created a MySQL table and now I want to find out what collation some of the fields are using. What SQL or MySQL commands can I use to discover this?

Answer 1

You could use SHOW FULL COLUMNS FROM tablename which returns a column Collation, for example for a table 'accounts' with a special collation on the column 'name'

mysql> SHOW FULL COLUMNS FROM accounts; +----------+--------------+-------------------+------+-----+---------+----------+ | Field    | Type         | Collation         | Null | Key | Default | Extra    | +----------+--------------+-------------------+------+-----+---------+----------| | id       | int(11)      | NULL              | NO   | PRI | NULL    | auto_inc | | name     | varchar(255) | utf8_bin          | YES  |     | NULL    |          | | email    | varchar(255) | latin1_swedish_ci | YES  |     | NULL    |          | ... 

Or you could use SHOW CREATE TABLE tablename which will result in a statement like

mysql> SHOW CREATE TABLE accounts; CREATE TABLE `accounts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `email` varchar(255) DEFAULT NULL, ... 

Answer 2

If you want the collation for just that specific column (for possible use with a subquery)...

SELECT COLLATION_NAME FROM information_schema.columns WHERE TABLE_SCHEMA = 'tableschemaname' AND TABLE_NAME = 'tablename' AND COLUMN_NAME = 'fieldname';