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

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 Format	Compact Storage Characteristics	Enhanced Variable-Length Column Storage	Large Index Key Prefix Support	Compression Support	Supported Tablespace Types	Required File Format
REDUNDANT	No	No	No	No	system, file-per-table	Antelope or Barracuda
COMPACT	Yes	No	No	No	system, file-per-table	Antelope or Barracuda
DYNAMIC	Yes	Yes	Yes	No	file-per-table	Barracuda
COMPRESSED	Yes	Yes	Yes	Yes	file-per-table	Barracuda

可以看出，支援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後表建立成功了。