MySQL - Character Set

MySQL - Character Sets and Collations

Understanding Character Sets and Collations

This is a fundamental concept for handling text data correctly in a database. It’s crucial to get it right to avoid data corruption or unexpected sorting behavior.

• Character Set: A set of symbols and their encodings. It defines which characters (letters, numbers, symbols) you can store. Think of it as a dictionary of allowed characters.
• Collation: A set of rules for comparing and sorting characters within a character set. It defines whether comparisons are case-sensitive (_cs), case-insensitive (_ci), or binary (_bin), and handles language-specific sorting rules.

For example, the utf8mb4 character set can store a vast range of Unicode characters. The utf8mb4_0900_ai_ci collation tells MySQL to compare utf8mb4 strings in a way that is accent-insensitive (ai) and case-insensitive (ci).

The utf8mb4 Best Practice

In modern web development, the standard and highly recommended character set is utf8mb4.

Important: Do not use the older utf8 character set.

The legacy utf8 character set in MySQL (which is an alias for utf8mb3) only supports a subset of Unicode characters and uses a maximum of three bytes per character. This means it cannot store 4-byte characters like many emojis (e.g., 👍) or certain Asian language characters. Using utf8 can lead to data truncation and errors.

utf8mb4 uses up to four bytes per character and fully supports the entire Unicode standard. Always use utf8mb4 for new projects.

Showing Available Character Sets

You can list all character sets supported by your MySQL server with the SHOW CHARACTER SET statement.

SHOW CHARACTER SET;

To find collations for a specific character set, you can use a LIKE clause:

SHOW COLLATION LIKE 'utf8mb4%';

The default collation for utf8mb4 in modern MySQL (8.0+) is utf8mb4_0900_ai_ci, which is excellent for most multilingual applications.

Setting Character Sets

The SET NAMES or SET CHARACTER SET statement is used to configure the character set for the current client connection. This ensures that data sent from your application to MySQL is interpreted correctly.

Syntax

-- This is the recommended command to set connection character set
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';

Running SET NAMES 'utf8mb4' is a shortcut that sets three session variables: character_set_client, character_set_connection, and character_set_results. This is crucial for your application’s database driver to do upon connecting.

Verification

You can verify the session variables after connecting:

SHOW VARIABLES LIKE 'character_set%';

You should see utf8mb4 for the client, connection, and results variables.

Best Practices for Configuration

For consistency, you should configure utf8mb4 at every level of your stack.

1. Server Level: In your MySQL configuration file (my.cnf or my.ini), set the defaults for all new databases. [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_0900_ai_ci
2. Database Level: When creating a database, explicitly define its character set and collation. CREATE DATABASE my_app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
3. Table Level: Tables will inherit from the database, but you can override it. CREATE TABLE my_table (...) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
4. Connection Level: Ensure your application’s database connector is configured to use utf8mb4. Most modern libraries do this by default or with a simple charset=utf8mb4 parameter in the connection string.

Converting an Existing Table

If you need to convert a legacy table (e.g., from latin1 or utf8) to utf8mb4, you can use the ALTER TABLE statement. Always back up your data before performing this operation.

Example: Converting a posts table

-- Step 1: Convert the table's default character set and collation
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- Note: The `CONVERT TO` clause also converts all character columns (CHAR, VARCHAR, TEXT)
-- to the new character set. This is the safest way to perform the conversion.

After conversion, you may need to adjust the length of some VARCHAR columns if they are part of a key, as utf8mb4 characters can take more space, potentially exceeding index length limits.