那些从MySQL5.6升级到5.7的坑--innodb_large_prefix

前言

升级有风险，参数需谨慎

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就可以了