Discover collation of a MySQL column

MysqlCollation

Mysql Problem Overview

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?

Mysql Solutions

Solution 1 - Mysql

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,
...

Solution 2 - Mysql

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';

Solution 3 - Mysql

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

Categories
Recommended Mysql Solutions
Recommended Collation Solutions

Previous Solution:

Is there a situation in which Dispose won't be called for a 'using' block?

C#DisposeUsing

Next Solution:

Convert String to int array in java

JavaArraysString

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionTrindazView Question on Stackoverflow
Solution 1 - Mysql0x4a6f4672View Answer on Stackoverflow
Solution 2 - MysqlHoldOffHungerView Answer on Stackoverflow
Solution 3 - MysqlExplosion PillsView Answer on Stackoverflow