Shulou(Shulou.com)06/01 Report--

This article mainly introduces the MySQL index length limit principle analysis, the article through the example code introduced in great detail, for everyone's study or work has a certain reference learning value, need friends can refer to the following

index

TextField does not support indexing

MySQL has a limit on the length of index fields

The length of each index column of innodb engine is limited to 767 bytes, and the sum of all index columns cannot be greater than 3072 bytes.

The length of each index column of myisam engine is limited to 1000 bytes, and the sum of the lengths of all constituent index columns cannot be greater than 1000 bytes.

The maximum length of a varchar is the length of a character. If the database character set is utf-8, one character is 3 bytes. Therefore, under utf-8 character set, the innodb engine cannot create a single-column index longer than 255 characters

Different mysql versions result in different index length limits

In MySQL version 5.5, innodb_large_prefix was introduced to disable large prefix indexing for compatibility with earlier versions of InnoDB that did not support large index key prefixes

Enable innodb_large_prefix to limit the length of a single index to 3072 bytes (but the total length limit for joint indexes is still 3072 bytes), and disable innodb_large_prefix to limit the length of a single index to 767 bytes

innodb_large_prefix is turned off by default in MySQL 5.5 and MySQL 5.6, and turned on by default in MySQL 5.7 and above

In MySQL version 8.0, innodb_large_prefix has been removed

that's why I can create index of 1024 characters (3072 byte under utf8) on my own machine (MySQL 8.0), but not on my server (MySQL 5.5

Script to test index length limits:

use test;drop table if exists test_index_len;create table test_index_len(long_char varchar(1025) primary key) ENGINE=InnoDB charset=utf8;use test;drop table if exists test_index_len;create table test_index_len( long_char varchar(24), origin_str varchar(1000), key test_index(long_char, origin_str)) ENGINE=InnoDB charset=utf8;

The above is all the content of this article, I hope to help everyone's study, but also hope that everyone a lot of support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.