SoFunction
Updated on 2025-04-14

MySQL proofreading rules conflict resolution

Error analysis

MySQL error 1267 shows that when comparing two fields, their proofreading rules (Collations) are inconsistent. utf8mb4_0900_ai_ci (MySQL 8.0 default) and utf8mb4_general_ci are two different rules that cannot be compared directly.

Solution steps

1. Locate conflict fields

Query the proofreading rules for all relevant fields:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'your_database' 
AND COLLATION_NAME IN ('utf8mb4_0900_ai_ci', 'utf8mb4_general_ci');

2. Temporary solution: Forced proofreading rules

Explicitly unify rules in the query:

SELECT * FROM table1 
JOIN table2 ON  COLLATE utf8mb4_0900_ai_ci = ;

3. Permanent solution: Modify the table structure

Proofreading rules for unified fields:

-- Modify a single field
ALTER TABLE table_name MODIFY column_name VARCHAR(255) COLLATE utf8mb4_0900_ai_ci;

-- Modify the entire table and its fields
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

4. Modify the database default proofreading rules

ALTER DATABASE your_database COLLATE utf8mb4_0900_ai_ci;

This rule is used by default for newly created tables after that, but existing tables need to be updated manually.

5. Check the connection settings

Make sure that the client connection parameters (such as JDBC URL) specify the correct character set:

jdbc:mysql://host/db?useUnicode=true&characterEncoding=utf8&connectionCollation=utf8mb4_0900_ai_ci

Things to note

  • Performance impact: Modifying large table structure may lock tables and require operation during low peak periods.
  • Data consistency: Changing the proofreading rules may affect the sorting and comparison results and require comprehensive testing.
  • Index Rebuild: Modifying the field rules will rebuild the index to ensure that there are sufficient resources.

Complete example

Assume that the name field of the orders and customers tables conflicts:

Step 1: Modify the table structure

ALTER TABLE orders MODIFY customer_name VARCHAR(255) COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE customers MODIFY name VARCHAR(255) COLLATE utf8mb4_0900_ai_ci;

Step 2: Verify the modification

SHOW CREATE TABLE orders;
SHOW CREATE TABLE customers;

Step 3: Test Query

SELECT * FROM orders JOIN customers ON orders.customer_name = ;

Summarize

The core of resolving conflicts in proofreading rules is to ensure that the rules of the comparison fields are consistent. It is recommended to permanently modify the table structure to unify the rules to avoid potential future problems. For temporary fixes or when structures cannot be modified, use the COLLATE keyword to force unify rules.

This is the end of this article about MySQL proofreading rule conflict resolution. For more related content on MySQL proofreading rule conflict, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!