What Does Collation Mean and Why Is It Significant?

Collation refers to the set of rules governing string comparison and sorting. These rules can determine case sensitivity and whether accented characters are treated differently (for instance, distinguishing between "a" and "å"). Proper collation setup is vital, especially when dealing with diverse languages that may have unique rules—for example, where certain characters are treated as a single letter. Neglecting this can lead to unexpected or incorrect query results.

How to Verify the Collation of MySQL/MariaDB Tables

To check the collation of your tables, you can utilize the INFORMATION_SCHEMA database, which offers metadata access for all tables. You can retrieve the collation information using the following SQL query (assuming the table name is t_name):

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 't_name';

The output will resemble the following:

It's important to note that collation may apply to specific columns. To check the collation for individual columns, you can query the INFORMATION_SCHEMA.COLUMNS table like this:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 't_name';

This will yield results showing the collation for each column:

Changing the Collation of a Database, Table, or Column

If you need to change the collation for an entire database, you can execute the following command:

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4;

To change the collation for a specific table, use:

ALTER TABLE t_name CONVERT TO CHARACTER SET utf8;

For individual columns, the command is:

ALTER TABLE t_name MODIFY c_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;