3 Ways to Get the Collation of a Column in MariaDB

Here are three ways to return the collation of a column in MariaDB.

The `SHOW COLUMNS` Statement

The SHOW COLUMNS statement has an optional FULL keyword, that, when used, will output column collation and comments, as well as the privileges you have for each column.

Here’s an example of running this statement against a table called Events:

SHOW FULL COLUMNS FROM Events;

Example result:

+-----------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field     | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-----------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| EventId   | int(11)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| EventName | varchar(255) | latin5_turkish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-----------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

The `information_schema.columns` Table

Another option is to query the information_schema.columns table. In this case, we can choose just the columns we’re interested in:

SELECT
    column_name, 
    character_set_name, 
    collation_name 
FROM information_schema.columns
WHERE table_name = 'Events'
AND column_name = 'EventName';

Example result:

+-------------+--------------------+-------------------+
| column_name | character_set_name | collation_name    |
+-------------+--------------------+-------------------+
| EventName   | latin5             | latin5_turkish_ci |
+-------------+--------------------+-------------------+

The `SHOW CREATE TABLE` Statement

Another option is to use SHOW CREATE TABLE, which returns the the CREATE TABLE statement of our table.

However, the character set and collation information for a column is only returned if they differ from the table’s default collation.

Here’s an example of running this statement against a table called Pets, where the columns don’t differ from the table’s default:

SHOW CREATE TABLE Pets;

Result:

+-------+------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+------------------------------+
| Pets  | CREATE TABLE `Pets` (
  `PetId` int(11) NOT NULL,
  `PetTypeId` int(11) NOT NULL,
  `OwnerId` int(11) NOT NULL,
  `PetName` varchar(60) NOT NULL,
  `DOB` date DEFAULT NULL,
  PRIMARY KEY (`PetId`),
  KEY `PetTypeId` (`PetTypeId`),
  KEY `OwnerId` (`OwnerId`),
  CONSTRAINT `pets_ibfk_1` FOREIGN KEY (`PetTypeId`) REFERENCES `PetTypes` (`PetTypeId`),
  CONSTRAINT `pets_ibfk_2` FOREIGN KEY (`OwnerId`) REFERENCES `Owners` (`OwnerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------+

In this example, the collation information did not differ, and so no collation information was returned for any columns.

Let’s change the collation on the PetName column:

ALTER TABLE Pets 
MODIFY PetName VARCHAR(255) 
    CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL;

And run SHOW CREATE TABLE again:

SHOW CREATE TABLE Pets;

Result:

+-------+------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-------+------------------------------+
| Pets  | CREATE TABLE `Pets` (
  `PetId` int(11) NOT NULL,
  `PetTypeId` int(11) NOT NULL,
  `OwnerId` int(11) NOT NULL,
  `PetName` varchar(255) CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL,
  `DOB` date DEFAULT NULL,
  PRIMARY KEY (`PetId`),
  KEY `PetTypeId` (`PetTypeId`),
  KEY `OwnerId` (`OwnerId`),
  CONSTRAINT `pets_ibfk_1` FOREIGN KEY (`PetTypeId`) REFERENCES `PetTypes` (`PetTypeId`),
  CONSTRAINT `pets_ibfk_2` FOREIGN KEY (`OwnerId`) REFERENCES `Owners` (`OwnerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------+

This time we can see the new character set and collation settings against the PetName column.

The SHOW COLUMNS Statement

The information_schema.columns Table

The SHOW CREATE TABLE Statement

The `SHOW COLUMNS` Statement

The `information_schema.columns` Table

The `SHOW CREATE TABLE` Statement