How to Solve MySQL's Illegal Mix of Collations Error (latin1_swedish_ci and utf8_general_ci)?

MySQL character set conflict error details and solutions

When processing large amounts of data, you may encounter the following error in MySQL:

<code>错误号：1267

操作“=”的字符集校对（latin1_swedish_ci，隐式）和（utf8_general_ci，可强制）不匹配</code>

This error occurs when MySQL attempts to compare data with different character set collations, resulting in an incompatibility. In this case, the error occurs during a query that compares the "keyword" column (assuming collation with "latin1_swedish_ci") to a string literal that may be collated with "utf8_general_ci".

Resolve character set conflicts

To fix this error, you can try a few things:

1. Set connection collation: Execute the following query to set the connection collation to "utf8_general_ci":

<code>SET collation_connection = 'utf8_general_ci';</code>

2. Modify database and table encodings: Convert character sets and collations of affected databases and tables:

<code>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;</code>

3. Escape strings: In some cases, you can try escaping a string literal to ensure it uses proper collation. For example, you could try enclosing the string in single quotes (') or double quotes (") to force concatenation collation.

Other notes

MySQL sometimes introduces "latin1_swedish_ci" collation for no apparent reason. To avoid this problem in the future, explicitly define character sets and collations for all database objects, including tables, columns, and stored procedures. You can prevent errors like this from happening by ensuring that all data is collated consistently.

The above is the detailed content of How to Solve MySQL's Illegal Mix of Collations Error (latin1_swedish_ci and utf8_general_ci)?. For more information, please follow other related articles on the PHP Chinese website!