Innodb索引字段长度，数据行长度的限制_索引长度,数据长度-CSDN博客

本文链接：https://blog.csdn.net/weixin_39004901/article/details/88835768

本文详细探讨了InnoDB存储引擎在MySQL中对索引字段长度和数据行长度的限制。在非严格模式下，索引键长度默认限制为767 bytes，可通过设置innodb_large_prefix等参数扩展至3072 bytes。数据行长度最大65535 bytes，但变长类型如varchar只存储部分数据，其余在外存储。同时区分了varchar与text/blob类型的存储差异。

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

一、关于索引字段长度限制
在给大字符类型建索引时，通常会碰到如下报错：
Specified key was too long; max key length is 767 bytes
例如：

*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> alter table test add key idx_name(name);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1

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 index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | idx_name | 1 | name | A | 0 | 191 | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

test是一张innodb表，字符集是utf8mb4，