The World of MySQL & MariaDB

Understanding MySQL Collation: How It Affects String Comparison, Sorting, and Multilingual Performance

Server Management
目次

1. Introduction

MySQL is one of the most widely used database management systems, and among its key features, the Collation setting plays a critical role in determining how string data is compared and sorted.

Importance of Collation

Without proper collation settings, search results may be incorrect or database performance may degrade. This becomes especially important in multilingual systems, where accurate character comparison and searching are essential.

This article explains MySQL collation in detail—its configuration, types, and key considerations—to help you manage your databases more effectively.

2. What Is Collation?

Collation defines the rules MySQL uses to compare and sort string values.

Role of Collation

  • String sorting: Determines how strings are ordered.
  • String comparison: Used in expressions such as WHERE name = 'Sagawa'.
  • Search accuracy: Affects multilingual compatibility.

Relationship with Character Set

Collation is closely tied to the character set. For example, the utf8 character set includes the following collations:

  • utf8_general_ci: Case-insensitive comparison.
  • utf8_bin: Binary (case-sensitive) comparison.

Naming Convention

character_set_name_comparison_method

Examples:

  • utf8_general_ci: Case-insensitive (ci = case insensitive).
  • utf8_bin: Binary comparison.

3. Collation Levels in MySQL

MySQL allows collation to be set at five levels:

Server Level

SHOW VARIABLES LIKE 'collation_server';

To change it, modify my.cnf and restart the server:

[mysqld]
collation_server=utf8mb4_unicode_ci

Database Level

ALTER DATABASE db_name DEFAULT COLLATE utf8mb4_unicode_ci;

Table Level

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Column Level

ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(255) COLLATE utf8mb4_unicode_ci;

String Literal Level

SELECT * FROM table_name WHERE column_name = 'value' COLLATE utf8mb4_bin;

4. Major Collation Types and Their Characteristics

utf8_general_ci

  • Features: Case-insensitive and fast comparison.
  • Note: Less accurate; not fully Unicode-compliant.

utf8_unicode_ci

  • Features: High-accuracy comparison based on Unicode standards.
  • Note: Slightly slower than utf8_general_ci.

utf8_bin

  • Features: Case-sensitive; requires exact matches.
  • Use case: Passwords and identifiers.

utf8mb4_unicode_ci

  • Features: Compliant with modern Unicode standards; supports multiple languages.
  • Use case: Applications handling emojis and special symbols.

5. How to Check and Change Collation

You can check or modify collation at the database, table, or column level.

Checking Collation

Database Collation

SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

Table Collation

SHOW TABLE STATUS WHERE Name = 'table_name';

Column Collation

SHOW FULL COLUMNS FROM table_name;

Changing Collation

Database

ALTER DATABASE database_name
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

Table

ALTER TABLE table_name
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Column

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

Temporary Collation Change

When comparing columns with different collations, use the COLLATE clause to avoid errors.

SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;

Always back up your data and check application compatibility before making changes.

6. Notes and Best Practices

When configuring collations in MySQL, consider the following precautions and practices to ensure optimal performance.

Important Notes

Mixing Different Collations

Comparing or joining columns with different collations may trigger errors.

  • Error example:
ERROR 1267 (HY000): Illegal mix of collations for operation '='
  • Solution: Use the COLLATE clause to standardize collations.
SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;

Changing Collation and Existing Data

Changing collation may affect how existing data is compared or searched.

Performance Impact

  • utf8mb4_unicode_ci is more accurate but slower than utf8_general_ci.
  • For large datasets, the choice of collation can significantly impact query speed.

Migration Issues

Ensure compatibility with applications and other systems when modifying collations.

Best Practices

1. Choose Collation Based on Requirements

  • Accuracy: Use utf8_unicode_ci or utf8mb4_unicode_ci for precise comparison.
  • Performance: Use utf8_general_ci for faster comparisons when accuracy is less critical.
  • Multilingual support: Prefer utf8mb4-based collations for emojis and special characters.

2. Maintain Consistency

  • Use the same collation across databases, tables, and columns to avoid mismatches.
  • Apply COLLATE only temporarily if necessary.

3. Test and Backup Before Changes

  • Test configuration changes in a staging environment.
  • Always back up production data before applying alterations.

4. Optimize Performance

  • Use high-performance collations for frequently queried columns (e.g., indexed ones).
  • Adjust collation per query if needed.

5. Use Latest Unicode Standards

For multilingual systems, use utf8mb4_0900_ai_ci for improved precision and compatibility.

7. Conclusion

MySQL collation determines how string data is compared and sorted within the database. This guide covered the concept, configuration methods, types, and best practices for effective collation management.

Key Takeaways

  1. Definition: Collation controls string comparison and sorting. Choosing the right one improves accuracy and performance.
  2. Multiple Levels: Collation can be set at the server, database, table, column, and literal levels.
  3. Main Types:
  • utf8_general_ci: Fast but less accurate.
  • utf8_unicode_ci: Accurate but slower.
  • utf8mb4_unicode_ci: Unicode-compliant and emoji-friendly.
  1. Configuration: SQL examples for checking and modifying collations were provided.
  2. Best Practices: Always test and back up before making changes.

Optimizing Collation Use

Collation directly affects string comparison and sorting behavior. By following the methods and best practices discussed here, you can maintain both accuracy and performance in your MySQL database systems.