How to Fix MySQL's 'Illegal Mix of Collations' Error?

Troubleshooting MySQL's "Illegal Mix of Collations" Error

Large datasets can sometimes trigger MySQL's frustrating "Illegal mix of collations" error. This happens when comparing data with inconsistent character sets and collations.

The Problem

Consider this query:

<code class="language-sql">SELECT COUNT(*) AS num
FROM keywords
WHERE campaignId = '12'
AND LCASE(keyword) = 'hello again 昔 ã‹ã‚‰ ã‚ã‚‹ å ´æ‰€'</code>

Running this might produce:

<code>Error Number: 1267
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='</code>

Solutions: String Escaping vs. Encoding Changes

There are two main approaches to fix this: escaping the problematic string, or altering database and table encodings.

Method 1: String Escaping (Temporary Fix)

Escaping the string can temporarily avoid the error:

<code class="language-sql">SET collation_connection = 'utf8_general_ci';

SELECT COUNT(*) AS num
FROM keywords
WHERE campaignId = '12'
AND LCASE(keyword) = 'hello again \"昔 ã‹ã‚‰ ã‚ã‚‹ å ´æ‰€\"'</code>

However, this is a band-aid solution and might create future problems.

Method 2: Altering Database and Table Encodings (Permanent Solution)

For a permanent fix, standardize your database and tables to use utf8_general_ci:

<code class="language-sql">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>

By unifying your connection collation and database/table encodings to utf8_general_ci, you ensure consistent data handling and eliminate the collation mismatch. This is the recommended approach.

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