Khắc phục giới hạn Index 767-Byte trong MySQL (utf8mb4)

Thông báo lỗi

Khi chuyển đổi sang utf8mb4 hoặc tạo bảng với các chỉ mục VARCHAR(255), bạn có thể gặp phải trở ngại khó chịu này:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Lỗi này thường xuất hiện trong quá trình thực hiện lệnh ALTER TABLE hoặc CREATE TABLE. Đây là một vấn đề phổ biến đối với các nhà phát triển khi nâng cấp từ chuẩn utf8 (utf8mb3) cũ (3 byte) lên hỗ trợ đầy đủ utf8mb4 (4 byte) – vốn là chuẩn cần thiết để hiển thị emoji và các biểu tượng toán học.

Nguyên nhân gốc rễ: Tại sao lại là 767 Byte?

Phép toán đằng sau lỗi này khá đơn giản. Trong các phiên bản MySQL cũ sử dụng định dạng InnoDB Antelope, các khóa chỉ mục (index key) bị giới hạn tối đa ở mức chính xác 767 byte.

Hãy so sánh cách các bộ mã ký tự khác nhau sử dụng không gian đó:

• Legacy utf8 (utf8mb3): 1 ký tự = tối đa 3 byte. 255 characters * 3 bytes = 765 bytes. Con số này vừa vặn nằm dưới giới hạn.
• Modern utf8mb4: 1 ký tự = tối đa 4 byte. 255 characters * 4 bytes = 1020 bytes. Con số này vượt quá giới hạn 767 byte, khiến hệ thống từ chối tạo chỉ mục.

MySQL luôn dự phòng không gian tối đa có thể cho một chỉ mục. Ngay cả khi dữ liệu hiện tại của bạn chỉ là văn bản chữ số đơn giản, cơ sở dữ liệu vẫn chuẩn bị cho trường hợp xấu nhất là ký tự 4 byte.

Cách khắc phục 1: Chuyển định dạng hàng sang DYNAMIC

Giải pháp hiệu quả nhất là chuyển sang định dạng tệp Barracuda. Định dạng này hỗ trợ "large prefixes," cho phép chỉ mục lên tới 3072 byte. Để sử dụng, bạn phải đặt định dạng hàng của bảng thành DYNAMIC hoặc COMPRESSED.

Cập nhật một bảng duy nhất:

ALTER TABLE your_table_name 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, 
ROW_FORMAT=DYNAMIC;

Đối với các bảng mới, hãy bao gồm định dạng trong câu lệnh CREATE của bạn:

CREATE TABLE example (
    id INT PRIMARY KEY,
    slug VARCHAR(255) NOT NULL,
    UNIQUE KEY (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

Cách khắc phục 2: Quy tắc '191' (Sửa nhanh)

Nếu bạn đang làm việc trên một hệ thống bị khóa quyền hạn và không thể thay đổi các biến máy chủ toàn cục, hãy thử quy tắc "191". Vì 767 chia cho 4 bằng 191,75, nên cột VARCHAR(191) là kích thước lớn nhất có thể khớp với giới hạn chỉ mục cũ.

ALTER TABLE users MODIFY username VARCHAR(191) CHARACTER SET utf8mb4;

Đây thường là con đường an toàn nhất cho các ứng dụng cũ. Bạn giữ nguyên giới hạn 767 byte nhưng thu nhỏ cột vừa đủ để cho phép các ký tự 4 byte mà không làm hỏng cấu trúc (schema).

Cách khắc phục 3: Cấu hình hệ thống toàn cục (MySQL 5.6)

Đối với những người quản lý hàng chục bảng, việc cập nhật từng bảng theo cách thủ công rất tẻ nhạt. Thay vào đó, bạn có thể thay đổi hành vi mặc định của máy chủ nếu có quyền SUPER. Hãy chạy các lệnh sau để bật large prefixes trên toàn hệ thống:

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = ON;
SET GLOBAL innodb_large_prefix = ON;

Lưu ý rằng mặc dù việc này cho phép các chỉ mục lớn hơn, bạn vẫn cần đảm bảo các bảng của mình sử dụng ROW_FORMAT=DYNAMIC để thực sự tận dụng được giới hạn mới.

Xác minh: Cách xác nhận lỗi đã được khắc phục

Đừng bao giờ mặc định rằng quá trình chuyển đổi đã thành công chỉ vì không có lỗi hiện ra. Hãy xác minh cấu trúc bên dưới bằng ba bước kiểm tra sau:

• Kiểm tra định dạng hàng (Row Format): Chạy lệnh SHOW TABLE STATUS LIKE 'your_table_name'\G và tìm dòng Row_format: Dynamic.
• Kiểm tra đối chiếu (Collation): Chạy lệnh SHOW FULL COLUMNS FROM your_table_name; để đảm bảo bạn thấy utf8mb4_unicode_ci.
• Kiểm tra bộ mã ký tự (Charset): Thử chèn một ký tự 4 byte như emoji: INSERT INTO your_table_name (column) VALUES ('🚀');.

Phòng ngừa & Các thực hành tốt nhất

Cách khắc phục triệt để nhất là nâng cấp lên MySQL 8.0+ hoặc MariaDB 10.2+. Trong các phiên bản này, DYNAMIC là định dạng hàng mặc định và large prefixes được bật sẵn. Bạn sẽ hiếm khi thấy lỗi này trên các hệ thống hiện đại trừ khi bạn cố gắng tạo chỉ mục cho một cột lớn hơn 768 ký tự.

Khi xử lý các quá trình chuyển đổi phức tạp, tôi nhận thấy rằng các ký tự đôi khi có thể bị xáo trộn trong quá trình truyền tải trước khi đến được cơ sở dữ liệu. Nếu bạn đang gỡ lỗi các hành vi chuỗi kỳ lạ, công cụ URL Encoder/Decoder của ToolCraft là một cách tiện lợi, tập trung vào quyền riêng tư để xác minh mã hóa dữ liệu của bạn trực tiếp trong trình duyệt. Nó giúp đảm bảo các chuỗi của bạn sạch sẽ trước khi bạn bắt đầu khắc phục sự cố chỉ mục DB.

Danh sách kiểm tra tóm tắt:

• Trường hợp tốt nhất: Nâng cấp lên MySQL 8.0.
• Trên bản 5.6: Bật innodb_large_prefix và đặt ROW_FORMAT=DYNAMIC.
• Giải pháp cuối cùng: Hạ VARCHAR(255) xuống VARCHAR(191) cho các cột có chỉ mục.