Discover collation of a MySQL column
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';
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,
...
SHOW CREATE TABLE [tablename]
will show you the collation of each column as well as the default collation.