Keywords: MySQL | Collation Conflict | Error 1267
Abstract: This paper provides an in-depth analysis of the common MySQL Illegal mix of collations error (Error Code 1267), exploring the root causes of character set and collation conflicts. Through practical case studies, it demonstrates how to resolve the issue by modifying connection character sets, database, and table configurations, with complete SQL operation examples and best practice recommendations. The article also discusses key technical concepts such as character set compatibility and Unicode support, helping developers fundamentally avoid such errors.
Problem Background and Error Analysis
In MySQL database operations, the mixed use of character sets and collations often leads to operation failures. Error code 1267 indicates that the system has detected incompatible character set combinations, which is particularly common when handling multilingual data or cross-database operations.
In-depth Analysis of Error Causes
The root cause of this error lies in MySQL's requirement for compatible character sets and collations when performing string comparison operations. When the system detects incompatible configurations such as latin1_swedish_ci and utf8_general_ci, it throws an exception.
Specifically, in the example SQL statement:
SELECT COUNT(*) as num from keywords WHERE campaignId='12' AND LCASE(keyword)='hello again 昔 㸂ã ã‚ã‚â„ å ´æ‰€'The problem occurs during the comparison between the LCASE(keyword) function call and the string literal. If the keyword column is configured with latin1_swedish_ci character set, while the connection character set or string literal uses utf8_general_ci, a conflict arises.
Solution Implementation
To resolve this issue, unified character set configuration is necessary. First, adjust the connection character set:
SET collation_connection = 'utf8_general_ci';Then convert the database and table character sets:
ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;Technical Details and Best Practices
Choosing utf8_general_ci as the unified character set offers significant advantages. UTF-8 encoding supports most global characters, including complex character sets such as Chinese, Japanese, and Korean, effectively preventing potential character display issues in the future.
When performing character set conversion, the importance of data backup should be noted. Although ALTER TABLE operations typically do not cause data loss, a complete backup is still recommended in production environments.
Preventive Measures and Extended Discussion
To avoid similar issues, it is recommended to define character set specifications during the database design phase. For new projects, utf8mb4 character set is recommended as it provides more complete Unicode support, including special characters like emojis.
In application development, ensure that database connection configurations are consistent with the actual database character sets. Regularly check system variables such as character_set_client, character_set_connection, etc., to ensure they are coordinated with the database character set configuration.