Sửa lỗi ERROR 1267: Illegal Mix of Collations trong MySQL

Lỗi Gặp Phải

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

MySQL đang báo rằng nó tìm thấy hai giá trị chuỗi có quy tắc collation khác nhau và không biết cái nào được ưu tiên. Thay vì tự đoán, MySQL từ chối thực thi.

Tình huống điển hình: JOIN giữa hai bảng mà một cột dùng utf8mb4_unicode_ci còn cột kia dùng utf8mb4_general_ci. Có thể một bảng được tạo sáu tháng trước và bảng kia mới tạo tuần trước — ai đó đã thay đổi collation mặc định của server ở giữa khoảng thời gian đó. Chỉ vậy thôi là đủ gây ra lỗi.

Nguyên Nhân

Mỗi giá trị chuỗi trong MySQL đều mang theo một collation. Việc so sánh hai chuỗi hoàn toàn ổn miễn là một collation được ưu tiên hơn. Cả hai đều được đánh dấu IMPLICIT có nghĩa là không cái nào thắng — MySQL từ chối tiếp tục và bạn nhận được lỗi 1267.

Các nguyên nhân thường gặp:

Các bảng được tạo vào các thời điểm khác nhau với cài đặt DEFAULT COLLATE khác nhau
Collation cấp cột ghi đè collation mặc định của bảng
Stored procedure hoặc function trả về giá trị với collation khác
Biến session collation_connection không đồng bộ với collation của bảng
Cơ sở dữ liệu được khôi phục từ server khác có cài đặt mặc định khác

Bước 1: Xác Định Vị Trí Xung Đột

Trước khi sửa bất cứ điều gì, hãy xác định chính xác những cột nào đang xung đột. Chạy lệnh này trên các bảng trong câu query đang lỗi:

-- Kiểm tra collation của một bảng cụ thể
SHOW FULL COLUMNS FROM your_table_name;

-- Quét tất cả cột chuỗi trong toàn bộ database
SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CHARACTER_SET_NAME,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext')
ORDER BY TABLE_NAME, COLUMN_NAME;

Kiểm tra thêm collation mặc định ở cấp database:

SELECT
  SCHEMA_NAME,
  DEFAULT_CHARACTER_SET_NAME,
  DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'your_database_name';

Thường bạn sẽ thấy sự pha trộn — một số cột hiển thị utf8mb4_unicode_ci, số khác là utf8mb4_general_ci. Đó chính là danh sách thủ phạm cần xử lý.

Bước 2: Khắc Phục Xung Đột Collation

Tùy chọn A — Dùng COLLATE trực tiếp trong câu query (không cần thay đổi schema)

Cách nhanh nhất để tháo chặn một câu query cụ thể. Không cần ALTER, không có downtime:

SELECT *
FROM table_a a
JOIN table_b b
  ON a.name COLLATE utf8mb4_unicode_ci = b.name COLLATE utf8mb4_unicode_ci
WHERE a.status = 'active';

Hoặc dùng CONVERT nếu character set cũng khác nhau:

SELECT *
FROM table_a a
JOIN table_b b
  ON CONVERT(a.name USING utf8mb4) COLLATE utf8mb4_unicode_ci
   = CONVERT(b.name USING utf8mb4) COLLATE utf8mb4_unicode_ci;

Hãy xem đây là giải pháp tạm thời. Nó ngăn được triệu chứng, nhưng xung đột collation giữa các cột vẫn còn đó bên dưới.

Tùy chọn B — ALTER cột bị lỗi (khuyến nghị)

Sửa trực tiếp vào nguyên nhân gốc rễ. Chọn collation bạn muốn chuẩn hóa, sau đó ALTER từng cột đang xung đột:

-- Một cột duy nhất
ALTER TABLE table_b
  MODIFY COLUMN name VARCHAR(255)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Nhiều cột trong một lần thực thi:

ALTER TABLE table_b
  MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  MODIFY COLUMN email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Tùy chọn C — Chuyển đổi toàn bộ bảng

Khi một nửa bảng bị sai collation, việc ALTER từng cột một sẽ rất mất thời gian. Hãy chuyển đổi tất cả cùng một lúc:

ALTER TABLE table_b
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Một câu lệnh duy nhất, tất cả các cột chuỗi đều được cập nhật. Lưu ý: MySQL sẽ khóa bảng trong quá trình ghi lại dữ liệu. Trên bảng 500K hàng, quá trình này có thể mất 30–60 giây — hãy lên lịch thực hiện vào giờ ít traffic.

Tùy chọn D — Sửa collation mặc định của database

Các bảng mới sẽ kế thừa collation mặc định của database. Nếu giá trị mặc định đó sai, mọi bảng bạn tạo sau này đều sẽ tiếp tục gây ra lỗi 1267:

ALTER DATABASE your_database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Quan trọng: thao tác này chỉ ảnh hưởng đến các bảng mới được tạo sau thời điểm này. Các bảng hiện có vẫn giữ nguyên collation cho đến khi bạn ALTER chúng một cách rõ ràng.

Bước 3: Xác Nhận Kết Quả

Chạy lại câu query đang lỗi — nó sẽ thực thi thành công. Sau đó xác nhận collation của các cột đã đúng chưa:

-- Xác nhận collation các cột đã được cập nhật
SHOW FULL COLUMNS FROM table_b;

-- Hoặc qua information_schema
SELECT COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'table_b';

Bạn muốn thấy utf8mb4_unicode_ci hiển thị nhất quán trên tất cả các cột đã chỉnh sửa. Bất kỳ cột nào còn sót lại sẽ gây ra lỗi 1267 ngay khi có ai đó query đến chúng.

Mẹo Hữu Ích

unicode_ci hay general_ci — chọn cái nào?

Trên MySQL 5.7 hoặc MariaDB, hãy dùng utf8mb4_unicode_ci. Nó tuân theo chuẩn Unicode và xử lý văn bản đa ngôn ngữ chính xác — ký tự có dấu, chữ CJK, v.v.

Đang dùng MySQL 8.0+? utf8mb4_0900_ai_ci là giá trị mặc định mới và nhanh hơn đáng kể cho các truy vấn sắp xếp nặng. Chỉ chuyển sang nó nếu bạn có thể cập nhật tất cả các bảng cùng một lúc — áp dụng nửa vời sẽ tái tạo lại vấn đề xung đột collation.

Quy tắc thực sự: chọn một collation và dùng nó ở khắp mọi nơi. Việc pha trộn chính là nguyên nhân gây ra lỗi 1267.

Cố định collation mặc định qua my.cnf

Cài đặt collation ở cấp server để mọi database và bảng mới đều tự động bắt đầu với cài đặt đúng:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

Khởi động lại MySQL sau khi chỉnh sửa. Các database được tạo sau đó — mà không có collation rõ ràng — sẽ kế thừa các giá trị này.

Stored procedure là nguồn gây xung đột collation

Các tham số của procedure có thể có khai báo collation riêng, âm thầm xung đột với các cột trong bảng. Kiểm tra một procedure đáng ngờ bằng:

SHOW CREATE PROCEDURE your_procedure_name;

Tìm CHARACTER SET và COLLATE trong danh sách tham số. Các xung đột ở đó rất dễ bỏ sót vì lỗi chỉ xuất hiện khi runtime — khi procedure thực sự so sánh các giá trị.

Sửa hàng loạt nhiều bảng cùng lúc

Có hàng chục bảng cần sửa? Hãy tạo các câu lệnh ALTER từ information_schema thay vì gõ tay từng cái:

SELECT CONCAT(
  'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS fix_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE';

Xem xét kỹ kết quả trước khi chạy trên môi trường production. Mỗi câu lệnh sẽ khóa bảng trong quá trình thực thi — hãy sắp xếp thứ tự thực hiện trong khung giờ bảo trì nếu kích thước bảng đáng kể.