MySQL 5.7 到 5.6，出現 Index column size too large. The maximum column size is 767 bytes

冬天perfect發表於2019-01-08
MySqlIndex

MySQL 5.7建表語句：

CREATE TABLE `4dfca143de64f9b67a59f2d4e8dde0e2_51941` (
  `姓名` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
  `年齡` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
  `住址` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`姓名`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

在MySQL 5.6執行報錯如下：

錯誤程式碼： 1709
Index column size too large. The maximum column size is 767 bytes.
 

解決辦法：

InnoDB Row Format Overview

Row FormatCompact Storage CharacteristicsEnhanced Variable-Length Column StorageLarge Index Key Prefix SupportCompression SupportSupported Tablespace TypesRequired File Format
REDUNDANTNoNoNoNosystem, file-per-tableAntelope or Barracuda
COMPACTYesNoNoNosystem, file-per-tableAntelope or Barracuda
DYNAMICYesYesYesNofile-per-tableBarracuda
COMPRESSEDYesYesYesYesfile-per-tableBarracuda

可以看出，支援innodb_large_prefix的row format只有dynamic 和compressed，而且它們需要的file format必須為Barracuda。

於是，檢視MySQL 5.7的預設引數配置：

mysql> SHOW VARIABLES LIKE '%innodb_version%'\G
*************************** 1. row ***************************
Variable_name: innodb_version
        Value: 5.7.20
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_large_prefix'\G
*************************** 1. row ***************************
Variable_name: innodb_large_prefix
        Value: ON
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_format'\G
*************************** 1. row ***************************
Variable_name: innodb_file_format
        Value: Barracuda
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table'\G
*************************** 1. row ***************************
Variable_name: innodb_file_per_table
        Value: ON
1 row in set (0.01 sec)

檢視MySQL 5.6的預設引數配置：

mysql> SHOW VARIABLES LIKE '%innodb_version%'\G
*************************** 1. row ***************************
Variable_name: innodb_version
        Value: 5.6.24
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_large_prefix'\G
*************************** 1. row ***************************
Variable_name: innodb_large_prefix
        Value: OFF
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_format'\G
*************************** 1. row ***************************
Variable_name: innodb_file_format
        Value: Antelope
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table'\G
*************************** 1. row ***************************
Variable_name: innodb_file_per_table
        Value: ON
1 row in set (0.00 sec)

從以上比較可以看出5.7和5.6之間引數的預設配置區別，然後我們將5.6的引數配置成和5.7一致

mysql> SET GLOBAL innodb_large_prefix=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)


mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> CREATE TABLE `4dfca143de64f9b67a59f2d4e8dde0e2_519411` (
    ->   `姓名` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
    ->   `年齡` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
    ->   `住址` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
    ->   PRIMARY KEY (`姓名`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

可以看到修改引數以及加上row_format=dynamic後表建立成功了。

相關文章