74

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?

Trindaz
  • 17,029
  • 21
  • 82
  • 111

3 Answers3

113

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,
...
0x4a6f4672
  • 27,297
  • 17
  • 103
  • 140
  • 2
    FYI, the SHOW CREATE TABLE only shows the column SET and COLLATE if different to the table defaults. Also, table defaults aren't shown if same as db defaults. – maxhugen May 18 '17 at 02:47
  • @maxhugen You are right but I think it's definitely a bad practice to not declare table charset and collation explicity. – Rick Apr 24 '23 at 15:45
22

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';
HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
3

SHOW CREATE TABLE [tablename] will show you the collation of each column as well as the default collation.

dKen
  • 3,078
  • 1
  • 28
  • 37
Explosion Pills
  • 188,624
  • 52
  • 326
  • 405