前言
升级有风险，参数需谨慎
innodb_large_prefix
关于innodb_large_prefix这个参数，在之前的“MySQL之最”中有提到过，那在5.6和5.7中的表现哪里不一样呢？更直观一点，举个例子：
在5.6中的表现：
$mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4708773
Server version: 5.6.16.12-20161009-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
mysql> CREATE TABLE `cc` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `c` varchar(255) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `idx_c` (`c`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table cc\G
*************************** 1. row ***************************
Table: cc
Create Table: CREATE TABLE `cc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c` (`c`(191))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> show global variables like "innodb_large_prefix";
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | OFF |
+---------------------+-------+
1 row in set (0.00 sec)在5.7中的表现
$mysql -uroot -p
Entry Port ==== 3313
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9304
Server version: 5.7.13.1-AliSQL-20160928-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@test 02:23:11>CREATE TABLE `cc` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `c` varchar(255) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `idx_c` (`c`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
root@test 02:25:46>show warnings;
Empty set (0.00 sec)
root@test 02:26:17>show global variables like "innodb_large_prefix";
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+
1 row in set (0.00 sec)
root@test 02:28:43>结论
如果想在5.6和5.7统一的话，修改innodb_large_prefix这个参数同时为ON或者OFF就可以了
