前言
MySQL支持的单列索引长度是3072bytes，单列索引长度限制取决于：字段类型、字符集、创建表指定的ROW_FORMA格式。
索引长度计算：
- 索引字段，如果NULL，则需要占用一个字节来记录值是否为NULL。
- 定长字段，如INT占4个字节、DATE占3个字节、CHAR(N)占N个字符。（注意字符与字节的区别，一个字符占用的字节取决于当前的字符集）
- 变长字段，如VARCHAR(N)占用N个字符+2个字节（记录长度）。
字符集长度
(root@localhost) [information_schema]> select * from information_schema.CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
| cp850 | cp850_general_ci | DOS West European | 1 |
| hp8 | hp8_english_ci | HP West European | 1 |
| koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 |
| latin1 | latin1_swedish_ci | cp1252 West European | 1 |
| latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 |
| swe7 | swe7_swedish_ci | 7bit Swedish | 1 |
| ascii | ascii_general_ci | US ASCII | 1 |
| ujis | ujis_japanese_ci | EUC-JP Japanese | 3 |
| sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 |
| hebrew | hebrew_general_ci | ISO 8859-8 Hebrew | 1 |
| tis620 | tis620_thai_ci | TIS620 Thai | 1 |
| euckr | euckr_korean_ci | EUC-KR Korean | 2 |
| koi8u | koi8u_general_ci | KOI8-U Ukrainian | 1 |
| gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 |
| greek | greek_general_ci | ISO 8859-7 Greek | 1 |
| cp1250 | cp1250_general_ci | Windows Central European | 1 |
| gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 |
| latin5 | latin5_turkish_ci | ISO 8859-9 Turkish | 1 |
| armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 |
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 |
| cp866 | cp866_general_ci | DOS Russian | 1 |
| keybcs2 | keybcs2_general_ci | DOS Kamenicky Czech-Slovak | 1 |
| macce | macce_general_ci | Mac Central European | 1 |
| macroman | macroman_general_ci | Mac West European | 1 |
| cp852 | cp852_general_ci | DOS Central European | 1 |
| latin7 | latin7_general_ci | ISO 8859-13 Baltic | 1 |
| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
| cp1251 | cp1251_general_ci | Windows Cyrillic | 1 |
| utf16 | utf16_general_ci | UTF-16 Unicode | 4 |
| utf16le | utf16le_general_ci | UTF-16LE Unicode | 4 |
| cp1256 | cp1256_general_ci | Windows Arabic | 1 |
| cp1257 | cp1257_general_ci | Windows Baltic | 1 |
| utf32 | utf32_general_ci | UTF-32 Unicode | 4 |
| binary | binary | Binary pseudo charset | 1 |
| geostd8 | geostd8_general_ci | GEOSTD8 Georgian | 1 |
| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |
| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |
| gb18030 | gb18030_chinese_ci | China National Standard GB18030 | 4 |
+--------------------+----------------------+---------------------------------+--------+创建索引
MySQL 8.X
MySQL 8.X默认支持的单列索引索引最大长度是3072bytes。
数据库版本：MySQL 8.0.24
字符集：utf8mb4（一个字符占4bytes）
索引字段：变长varchar(n)
创建表
CREATE TABLE `items` (
`itemid` bigint unsigned NOT NULL,
`key_info` varchar(2048) DEFAULT '' NOT NULL,
PRIMARY KEY (itemid)
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8MB4;
计算支持的最大单列索引长度是(3072-2)/4=767.5
指定索引长度为767
(root@localhost) [test]> CREATE INDEX `index_items_key` ON `items` (`key_info`(767));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
指定索引长度为768
(root@localhost) [test]> CREATE INDEX `index_items_key` ON `items` (`key_info`(768));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
指定索引长度为大于768则失败，报key超过了最大长度
(root@localhost) [test]> CREATE INDEX `index_items_key` ON `items` (`key_info`(769));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytesMySQL 5.7
MySQL 5.7默认支持的单列索引索引最大长度是3072bytes，可以通过修改innodb_large_prefix = OFF改变默认支持长度为767bytes。
引用官档：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_file_format
数据库版本：MySQL 5.7.33
字符集：utf8mb4（一个字符占4bytes）
索引字段：变长字段varchar(n)
创建表
CREATE TABLE `items` (
`itemid` bigint unsigned NOT NULL,
`key_info` varchar(2048) DEFAULT '' NOT NULL,
PRIMARY KEY (itemid)
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8MB4;
计算支持的最大单列索引长度是(3072-1-2)/4=767.5
指定索引长度为767
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(767));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
指定索引长度为768
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(768));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
指定索引长度大于768则会失败
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(769));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
调整innodb_large_prefix = OFF，则会限制索引单列的最大长度为767bytes。
(root@localhost) [gaoyu]> set @@global.innodb_large_prefix = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
计算支持的最大单列索引长度是(767-1-2)/4=191
指定索引长度为191
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(191));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
指定索引长度为192 VARCHAR(192)则会失败
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(192));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytesMySQL 5.6
MySQL 5.6默认支持的单列索引索引最大长度是767bytes，可以通过修改innodb_large_prefix = ON、innodb_file_format = barracuda、innodb_file_per_table = true以及创建表时row_format=(DYNAMIC 或 COMPRESSED)更改默认支持长度为3072bytes。
引用官档：https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
数据库版本：MySQL 5.6.21
字符集：utf8mb4（一个字符占4bytes）
索引字段：变长字段varchar(n)
创建表
CREATE TABLE `items` (
`itemid` bigint unsigned NOT NULL,
`key_info` varchar(2048) DEFAULT '' NOT NULL,
PRIMARY KEY (itemid)
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8MB4;
计算支持的最大单列索引长度是(767-1-2)/4=191
指定索引长度为191
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(191));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
指定索引长度为192则失败
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(192));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
变更参数，此时支持的索引最大长度是3072bytes
set @@global.innodb_large_prefix = ON;
set @@global.innodb_file_format = barracuda;
set @@global.innodb_file_per_table = true;
重新创建表并指定row_format=DYNAMIC 或 COMPRESSED
CREATE TABLE `items` (
`itemid` bigint unsigned NOT NULL,
`key_info` varchar(2048) DEFAULT '' NOT NULL,
PRIMARY KEY (itemid)
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8MB4 ROW_FORMAT=DYNAMIC;
计算支持的最大单列索引长度是(3072-1-2)/4=767.5
指定索引长度为767
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(767));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
指定索引长度为768
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(768));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
索引长度大于768则会失败
(root@localhost) [gaoyu]> CREATE INDEX `index_items_key` ON `items` (`key_info`(769));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· 没有源码，如何修改代码逻辑？
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 记一次.NET内存居高不下排查解决与启示