SoFunction
Updated on 2025-03-05

MySQL error 1267 - Illegal mix of collations problem solution

I enter the following code

SELECT 
*
FROM 
po_pomainviewt
WHERE cInvName IN (
    SELECT SUBSTRING_INDEX(FileName, '.', 1)
    FROM fileinfo
);

mistake

Report an error1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_german2_ci,IMPLICIT) for operation '='

This error is due to inconsistent character set collations of the two tables when executing the query. You can solve this problem in the following ways:

1. Use COLLATE to specify the same sorting rules

You can specify it explicitly in the queryCOLLATE, make the sorting rules on both sides consistent. For example:

SELECT *
FROM po_pomainviewt
WHERE cInvName COLLATE utf8mb4_german2_ci IN (
    SELECT SUBSTRING_INDEX(FileName, '.', 1) COLLATE utf8mb4_german2_ci
    FROM fileinfo
);

In this example, I usedutf8mb4_german2_ciSorting rules. You can also change it to other sorting rules that suit you as needed.

2. Modify the table's sorting rules

You can modify the table's collation to keep it consistent throughout the table:

ALTER TABLE po_pomainviewt CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci;
ALTER TABLE fileinfo CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci;

3. Unified database sorting rules

You can also modify the database collations to make all new tables use the same collations:

ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci;

This method directly compares the binary values ​​of characters without being affected by the sorting rules.

Choose the method that best suits your current situation to deal with it.

Summarize

This is the article about mysql error 1267 - Illegal mix of collations problem solution. For more related mysql error 1267, please search for my previous articles or continue browsing the related articles below. Illegal mix of collations content, Illegal mix of collations content, please search for my previous articles or continue browsing the related articles below. Illegal mix of collations. Illegal mix of collations, I hope everyone will support me in the future!