MySQL Collation

MySQL Collation

1. What is Collation in MySQL?

A collation in MySQL determines how string comparisons and sorting are performed based on a specific character set. It defines rules for comparing and ordering text values.

Each character set in MySQL has one or more collations associated with it.

2. Checking Available Collations

To list all collations in MySQL, run:

SHOW COLLATION;

To check the default collation for the MySQL server:

SHOW VARIABLES LIKE 'collation_server';

To check the collation of a specific database:

SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'your_database';

To check the collation of a specific table:

SHOW TABLE STATUS WHERE Name = 'your_table';

To check the collation of a specific column:

SHOW FULL COLUMNS FROM your_table;

3. Choosing the Right Collation

Common Collations and Their Uses

Collation	Description	Case-Sensitive?	Accent-Sensitive?
utf8mb4_general_ci	General Unicode collation (fast, but less accurate)	❌ No	❌ No
utf8mb4_unicode_ci	Unicode-compliant collation (better sorting accuracy)	❌ No	❌ No
utf8mb4_unicode_520_ci	Improved Unicode sorting (MySQL 5.6+)	❌ No	❌ No
utf8mb4_bin	Binary collation (case-sensitive and accent-sensitive)	✅ Yes	✅ Yes
utf8mb4_general_cs	Case-sensitive Unicode collation	✅ Yes	❌ No

💡 Best Practice:

• Use utf8mb4_unicode_ci for accurate multilingual sorting.
• Use utf8mb4_general_ci for better performance in non-strict sorting.
• Use utf8mb4_bin when case-sensitive matching is required.

4. Setting Collation in MySQL

A. Setting Collation for a Database

When creating a database:

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To change the collation of an existing database:

ALTER DATABASE mydatabase COLLATE utf8mb4_unicode_ci;

B. Setting Collation for a Table

When creating a table:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

To change the collation of an existing table:

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

C. Setting Collation for a Column

ALTER TABLE users MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

D. Setting Collation for a Query

Use the COLLATE keyword to override the default collation in queries:

SELECT * FROM users WHERE name = 'John' COLLATE utf8mb4_bin;

5. Collation Differences in Sorting and Comparison

A. Case Sensitivity

SELECT 'apple' = 'Apple' COLLATE utf8mb4_general_ci; -- Returns 1 (Equal)
SELECT 'apple' = 'Apple' COLLATE utf8mb4_bin; -- Returns 0 (Not Equal)

🔹 utf8mb4_general_ci is case-insensitive.
🔹 utf8mb4_bin is case-sensitive.

B. Accent Sensitivity

SELECT 'résumé' = 'resume' COLLATE utf8mb4_general_ci; -- Returns 1 (Equal)
SELECT 'résumé' = 'resume' COLLATE utf8mb4_unicode_ci; -- Returns 0 (Not Equal)

🔹 utf8mb4_general_ci treats accents as equal.
🔹 utf8mb4_unicode_ci treats accents as different.

6. Converting an Existing Database to a New Collation

If you need to convert an entire database to a different collation:

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To convert all tables:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To convert all columns in a table:

ALTER TABLE mytable MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

7. Best Practices for Using Collations

✔ Use utf8mb4_unicode_ci for general multilingual support.
✔ Use utf8mb4_general_ci if you need faster performance but less accurate sorting.
✔ Use utf8mb4_bin for strict case-sensitive and accent-sensitive comparisons.
✔ Always specify character set and collation when creating a database or table.
✔ Check collation mismatches between tables to avoid JOIN issues.

Summary

• Collation determines sorting and text comparisons in MySQL.
• Case-insensitive collations (utf8mb4_general_ci) treat 'A' and 'a' as the same.
• Case-sensitive collations (utf8mb4_bin) treat 'A' and 'a' as different.
• Use utf8mb4_unicode_ci for the best multilingual support.
• Always ensure collation consistency across your database to avoid query issues.

Would you like help changing the collation in your database? 🚀