业务侧反馈说在 mysql 5.7 中建表的时候报了如下错误：

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.http://www.cndba.cn/dave/article/131559

这个是 5.6 的遗留问题，在 5.7 中已经解决了。但业务说的那么确认，还是重新验证了一下，将业务的 SQL 拿到 5.7 的环境中执行，确实没有这个问题。

mysql> select @@version;
+---------------+
| @@version     |
+---------------+
| 5.7.44-48-log |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE ustc (
    ->   `id` bigint(20) ,
    ->   `url` varchar(255),
    ->   INDEX idx_config_key(url) USING BTREE
    -> )  ROW_FORMAT=DYNAMIC CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ;
Query OK, 0 rows affected (0.01 sec)

8.4 官网的说明：

https://dev.mysql.com/doc/refman/8.4/en/innodb-limits.html

The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.
The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

http://www.cndba.cn/dave/article/131559

http://www.cndba.cn/dave/article/131559

5.7 官网的说明：

https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html
If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.
innodb_large_prefix is deprecated; expect it to be removed in a future MySQL release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.
The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character.http://www.cndba.cn/dave/article/131559http://www.cndba.cn/dave/article/131559

可以看到，到了 5.7 以后的版本，在innodb_large_prefix 参数启用（默认值），并且 row format使用DYNAMIC 或 COMPRESSED的情况下，index key prefix 的限制是 3072. 只有innodb_large_prefix 禁用的情况下（5.6），index key prefix 才是 767 bytes。

如果表的字符集是utf8mb4，默认支持的索引字段最大长度是191字符（767字节/4字节每字符≈191字符），因此在varchar(255)或char(255)类型字段上创建索引会失败。

在 MySQL 5.7 中，参数默认就是支持建立长字节索引的：

mysql> show variables like '%row_format%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

MySQL 5.6 的参数 innodb_large_prefix 默认为 off ，所以对于 5.6 的环境，可以考虑启用innodb_large_prefix，然后在 create/alert table 时加上ROW_FORMAT=DYNAMIC。

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

mysql> set global innodb_default_row_format = dynamic;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE ustc3 (
    ->   `id` bigint(20) ,
    ->   `url` varchar(255),
    ->   INDEX idx_config_key(url) USING BTREE
    -> ) ROW_FORMAT=DYNAMIC CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ;
Query OK, 0 rows affected (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。