MySQL - Collation

MySQL Character Sets and Collations: A Modern Guide

In MySQL, a character set is a collection of symbols and their encodings, while a collation is a set of rules for comparing and sorting characters within that set. Getting this right is crucial for storing international data correctly and ensuring queries behave as expected.

Think of a character set as the alphabet (e.g., English alphabet) and a collation as the dictionary’s sorting rules (e.g., should ‘Apple’ come before or after ‘apple’?). Every character set has at least one default collation.

Best Practice: Use utf8mb4

For modern applications, the recommended character set is utf8mb4. It provides full Unicode support, including emojis and various international symbols, which the older utf8 (an alias for utf8mb3 in modern MySQL) does not. Using utf8mb4 from the start prevents future data corruption or migration headaches.

The recommended collation for utf8mb4 in MySQL 8.0 and later is utf8mb4_0900_ai_ci. Let’s break down its name:

• utf8mb4: The character set.
• 0900: Based on Unicode version 9.0.0, which provides more accurate sorting for a wide range of languages.
• ai: Accent-insensitive. It treats ‘a’, ‘á’, and ‘ä’ as the same character for sorting purposes.
• ci: Case-insensitive. It treats ‘a’ and ‘A’ as the same character for sorting.

Other common collation types include _as_cs (accent-sensitive, case-sensitive) for strict matching, and _bin (binary) which compares characters based on their binary byte values.

Setting Character Set and Collation

You can define the character set and collation at four different levels. MySQL uses a hierarchy: column settings override table settings, which override database settings, which override server settings.

1. Server Level

You can configure the default character set and collation for the entire MySQL server in your configuration file (my.cnf or my.ini). This ensures all new databases and tables use modern defaults.

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

2. Database Level

You can specify the defaults for a specific database during its creation or by altering it later. This is the most common and recommended approach for managing application-specific settings.

-- Creating a new database with modern defaults
CREATE DATABASE my_app_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- Updating an existing database
ALTER DATABASE existing_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

3. Table Level

You can override the database defaults for a specific table. This might be useful for tables with special linguistic requirements.

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
)
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;

4. Column Level

For the highest level of control, you can specify the character set and collation for an individual text-based column. This is useful if, for example, a column needs case-sensitive comparisons while the rest of the table does not.

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(100) COLLATE utf8mb4_0900_as_cs, -- Case-sensitive for usernames
  bio TEXT -- Inherits table's default collation (case-insensitive)
);

Verifying Collations

You can list all available character sets and their default collations using SHOW CHARACTER SET.

SHOW CHARACTER SET;

To find all available collations for a specific character set or matching a pattern, you can use SHOW COLLATION with a WHERE or LIKE clause.

Example: Finding utf8mb4 collations

SHOW COLLATION WHERE Charset = 'utf8mb4';

Example Output (abbreviated)

Collation	Charset	Id	Default	Compiled	Sortlen
utf8mb4_0900_ai_ci	utf8mb4	255	Yes	Yes	0
utf8mb4_0900_as_cs	utf8mb4	305		Yes	0
utf8mb4_bin	utf8mb4	46		Yes	1
utf8mb4_general_ci	utf8mb4	45		Yes	1

Note: User-defined collations are deprecated in modern MySQL versions and their use is strongly discouraged.