今天在一台安裝了 MySQL 5.5 的主機上, 建立資料表時, 出現以下錯誤:
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
要看到這個錯誤, 可以在 MySQL 5.5/5.6 (或 Mariadb) 上嘗試執行以下指令建立資料庫:
MariaDB [(none)]> use opencli;
MariaDB [opencli]> create table test (
-> id int not null,
-> code_value1 varchar(500) not null,
-> code_value2 varchar(500) not null,
-> unique index unique_index (id, code_value1, code_value2)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
以上 SQL Query 在 MySQL 5.7 執行並沒有問題, 這個題題在 MySQL 5.6 的官方文件 “Limits on InnoDB Tables” 可以找到原因:
By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.
Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the master if it cannot also be enabled on slaves.
上面有提到可以開啟 innodb_large_prefix 解決問題。在 MySQL 5.7, innodb_large_prefix 預設是開啟, 而在 MySQL 5.5 及 MySQL 5.6 則預設是關閉, 而往後的 MySQL 版本會將 innodb_large_prefix 刪除, 只會在開啟的狀態。關閉的原因主要是想與較舊的 MySQL 5.1 版本兼容。
要解決這個問題便要開啟 innodb_large_prefix, 同時將 innodb_file_format 設定為 “barracuda” (預設是 “Antelope”), 以及資料表的 ROW_FORMAT 要設定為 “DYNAMIC”, 可以執行以下指令解決:
MariaDB [(none)]> use opencli;
MariaDB [opencli]> set global innodb_file_format = BARRACUDA;
MariaDB [opencli]> set global innodb_large_prefix = ON;
MariaDB [opencli]> create table test (
-> id int not null,
-> code_value1 varchar(500) not null,
-> code_value2 varchar(500) not null,
-> unique index unique_index (id, code_value1, code_value2)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)
以上設定只會暫時生效, 當 MySQL 重新啟動後便會失效, 要保留在下次重新啟動後有效, 需要修改 my.cnf:
在 [mysqld] 段落, 加入以下兩行:
innodb-file-format = BARRACUDA
innodb-large-prefix = ON
這樣下次重新啟動 MySQL 後便會保留這兩個設定。