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

最新推荐文章于 2025-06-12 10:42:12 发布

转载最新推荐文章于 2025-06-12 10:42:12 发布 · 2.6k 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://juejin.im/post/5a814463f265da4e8700caed

文章标签：

#数据库 #php

本文通过对比 MySQL 5.6 和 5.7 中创建表时使用 innodb_large_prefix 参数的表现，详细解析了该参数在两个版本之间的差异，并给出了统一配置的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

摘要：

前言升级有风险，参数需谨慎 innodb_large_prefix 关于innodb_large_prefix这个参数，在之前的“MySQL之最”中有提到过，那在5.6和5.7中的表现哪里不一样呢？更直观一点，举个例子：在5.

前言

升级有风险，参数需谨慎

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>复制代码