MySQL Character Sets and Collation Rules

🌐 中文

English

This article aims to provide a systematic discussion on MySQL character sets and collation rules.

What are MySQL Character Sets and Collation Rules?

Character Set: A character set defines the characters to be stored and their encoding methods. For example, utf8mb4 is a character set that uses 4 bytes per character and is based on the Unicode character standard, while latin1 is a character set that uses 1 byte per character and is based on the ISO-8859-1 standard for Western European characters. Collation Rules: Collation rules define how strings are sorted and compared based on the character set. For example, utf8mb4_general_ci (the collation method in MySQL 5.7, with MySQL 8.0 primarily using utf8mb4_0900_ai_ci) is a collation rule based on the utf8mb4 character set, which is case-insensitive and accent-insensitive.

Classification of Character Sets and Collation Rules

Classification of Character Sets

1. Single-byte character sets: Such as latin1, cp1252, etc., where each character occupies 1 byte.
2. Multi-byte character sets: Such as utf8, utf16, etc., where each character occupies 1-4 bytes.
3. Variants of multi-byte character sets: Such as utf8, utf8mb4, where each character occupies 1-4 bytes and supports more Unicode characters.
4. Non-Unicode character sets: Such as gbk, big5, etc., where each character occupies 2 bytes.

Classification of Collation Rules

1. _ai: Accent Insensitive
2. _as: Accent Sensitive
3. _ci: Case Insensitive
4. _cs: Case Sensitive
5. _bin: Binary comparison
6. _general: Loose collation rules (not strictly following Unicode standards)
7. unicode: Strict collation rules (strictly following Unicode standards) (Note: _ai and _as are collation rules for accents, _ci and _cs are for case sensitivity, _bin is for binary comparison, and _as_cs has advantages over _bin in terms of local language comparison.)

Levels of Collation Rules

1
2
3
4
5
# There are four levels of character sets and collation rules: (from highest to lowest)
# 1. Server level  server
# 2. Database level  database
# 3. Table level      table
# 4. Column level      column

If character sets and collation rules are not specified, lower-level ones default to the higher-level ones.

Practical MySQL Commands

1. View character sets and collation rules

   1
   SHOW variables like '%character%';;
   

2. View the character sets and collation rules of the current database and table

   1
   2
   SHOW CREATE DATABASE db_name;
   SHOW CREATE TABLE table_name;
   

3. Modify character sets and collation rules

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   # Modify the character set and collation rule of a database
   ALTER DATABASE db_name DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE = utf8mb4_general_ci;
   # Modify the character set and collation rule of a table
   ALTER TABLE table_name CONVERT TO CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
   # Modify the character set and collation rule of a column
   ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(255) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
   # Modify the character sets of character_set_client, character_set_connection, and character_set_results
   (Modify character sets at the session level; global changes require modification in the configuration file or specifying them one by one in the MySQL command line.)
   SET NAMES utf8mb4;
   # Specify modification of session character set variables
   SET @@session.character_set_client = utf8mb4;
   # Specify modification of session collation variables
   SET @@session.collation_connection = utf8mb4_general_ci;
   # Specify modification of global character set variables
   SET GLOBAL character_set_server = utf8mb4;
   # Specify modification of global collation variables
   SET @@global.collation_server = utf8mb4_general_ci;
   

   Since it is best practice to properly plan character sets, collation rules, and field default values from the beginning when designing a database, as this reduces modification costs and potential risks in subsequent maintenance, it is generally not recommended to modify the character sets and collation rules of databases and tables.

4. Specify character sets and collation rules during creation

   1
   2
   3
   4
   5
   6
   # Specify character set and collation rule when creating a database
   CREATE DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
   # Specify character set and collation rule for a column when creating a table
   CREATE TABLE table_name (
    column_name VARCHAR(255) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci
   );
   

Summary

1. Character sets and collation rules are important concepts in MySQL database design, as they determine how data is stored and compared.
2. Choosing appropriate character sets and collation rules during database design can improve database performance and reliability.
3. When modifying the character sets and collation rules of databases and tables, careful consideration must be given to data migration and compatibility issues.
4. When creating databases and tables, character sets and collation rules can be specified during creation to ensure data consistency and reliability.
5. When modifying character sets and collation rules, pay attention to the differences between session-level and global-level changes.
   This concludes my systematic discussion on MySQL character sets and collation rules.