Index – Specified key was too long

Tìm hiểu nguyên nhân và các xử lý khi gặp phải lỗi “Specified key was too long” với MySQL.

Mở đầu

Nếu bạn đã từng làm việc với MySQL – hệ quản trị CSDL quan hệ thì chắc hẳn bạn đã từng ít nhất một lần gặp phải khó khăn trong khi khai báo index (unique chẳng hạn) cho một cột trong bảng. Tôi đang nói đến một thông báo lỗi tương tự như “Specified key was too long”. Hôm nay, tôi sẽ cùng bạn tìm hiểu nguyên nhân và cách xử lý lỗi này.

Chuẩn bị

Để tái hiện lỗi này, bạn cần cài đặt MySQL cho máy tính của mình trước tiên. Ở đây, tôi đang sử hệ điều hành Ubuntu 16.04 và đã cài đặt MySQL 5.7 rồi. Nhưng vì tôi muốn tìm hiểu và giải thích rõ hơn nên tôi sẽ sử dụng Docker để dựng tối thiểu hai phiên bản MySQL là 5.6 và 5.7. Bạn có thể tham khảo thông số môi trường của tôi dưới đây:

  • Ubuntu: 16.06-Xenial
  • Docker: Docker version 18.09.0, build 4d60db4
  • Docker Compose: docker-compose version 1.21.0, build 5920eb0
  • Docker Compose File (docker-compose.yml)

Mở terminal bằng phím tắt Ctrl + Alt + T. Sau đó, tôi đi chuyển đến thư mục chứa tệp docker-comppose.yml. Chạy lệnh “docker-compose up -d” rồi chờ đợi một chút là tôi đã có môi trường phù hợp.

Tái hiện

MySQL 5.6

MySQL 5.7

Các bạn có thể thấy rằng cùng một thông số CSDL và bảng mà với MySQL 5.6, chúng ta đã gặp một thông báo lỗi “ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”; còn với MySQL 5.7, tất cả hoạt động bình thường. Liệu có phải với MySQL 5.7, lỗi này sẽ không bao giờ xảy ra? Câu trả lời là “Không”. Nó hoàn toàn có thể xảy ra” nếu tôi tăng số lượng ký tự của cột muốn thiết lập index lên tới 769 ký tự.

Giải thích

Thứ nhất, với charset utf8mb4, mỗi một ký tự được lưu trữ sẽ chiếm 1-4 bytes. Có nghĩa là chuỗi ký tự lưu trữ trên một bản ghi thuộc cột được khai báo “varchar(255)” có thể chiếm tối đa 255 * 4 = 1020 (bytes).

Thứ hai, engine mặc định khi tạo CSDL trong MySQL là InnoDB. Nó có một thông số quy định độ dài tiền tố của index (index prefix key length) là innodb_large_prefix. Nếu innodb_large_prefix=”OFF”, index prefix key length mặc định là 767 bytes.

Quay lại ví dụ trên, MySQL 5.6 có innodb_large_prefix mặc định là “OFF” nên tôi khai báo unique cho “code varchar(255)” đã vượt giới hạn cho phép vì 225 * 4 = 1020 (bytes) > 768 (bytes). Đó là lý do tôi nhận được thông báo lỗi. Khác với bản 5.6, MySQL 5.7 có innodb_large_prefix mặc định là “ON” nên chỉ khi khai báo “code varchar(769)” thì tôi mới nhận được thông báo lỗi vì 769 * 4 = 3076 (bytes) > 3072 (bytes).

Xử lý

Căn cứ vào nguyên đã được tôi giải thích phía trên. Với điều kiện giữ nguyên engine InnoDB và charset utf8mb4, tôi sẽ có hai cách xử lý dưới đây:

  • Cách 1: Giữ nguyên thông số innodb_large_prefix (index prefix keylength = 767 (bytes)) và thay đổi số lượng ký tự của cột. Tôi sẽ giảm số lượng ký tự cho cột code xuống còn 191 vì 191 * 4 = 764 (bytes) < 767 (bytes).
  • Cách 2: Giữ nguyên số lượng cột và bật thông số innodb_large_prefix=”ON”. 255 * 4 = 1020 (bytes) < 3072 (bytes).

Lời kết

Như vậy, tôi và các bạn đã cùng nhau tìm hiểu được nguyên nhân cũng như cách xử lý lỗi “Specified key was too long” trong MySQL. Tôi hy vọng các bạn có thể xử lý một cách dễ dàng khi gặp lại vấn đề này.

Chúc các bạn học tập và làm việc vui vẻ!

Tài liệu

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *