InnoDB UTF8 Character Set varchar index length problem in MySQL

Source: Internet
Author: User

The limit for total index length is:

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)

In the UTF8 character set, the [data portion] of varchar (255) occupies 255*3=765 bytes, the closest 767bytes, 256*3 = 768bytes, already over 767.

[BIGHD] (root@localhost) [cm]> CREATE TABLE ' temp_2 ' (
-> ' id ' BIGINT () UNSIGNED not NULL auto_increment,
-> ' PN ' VARCHAR (128) DEFAULT ',
-> ' First_path ' VARCHAR (256) DEFAULT ',
-> ' dir ' VARCHAR (255) DEFAULT ',
-> ' A ' text,
-> PRIMARY KEY (' id ')
->) Engine=innodb DEFAULT Charset=utf8
->
->;
Query OK, 0 ROWS affected (0.15 sec)

[BIGHD] (root@localhost) [cm]>
[BIGHD] (root@localhost) [cm]>
[BIGHD] (root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (First_path);
Query OK, 0 ROWS affected, 1 Warning (0.18 sec)
records:0 duplicates:0 warnings:1

[BIGHD] (root@localhost) [cm]> 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)

[BIGHD] (root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (dir);
Query OK, 0 ROWS affected (0.12 sec)
records:0 duplicates:0 warnings:0

[BIGHD] (root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (A (767));
Query OK, 0 ROWS affected, 1 Warning (0.32 sec)
records:0 duplicates:0 warnings:1

[BIGHD] (root@localhost) [cm]> 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)

[BIGHD] (root@localhost) [cm]> show CREATE TABLE temp_2;
| temp_2 | CREATE TABLE ' temp_2 ' (
' ID ' BIGINT UNSIGNED not NULL auto_increment,
' PN ' VARCHAR (128) DEFAULT ',
' First_path ' VARCHAR (256) DEFAULT ',
' dir ' VARCHAR (255) DEFAULT ',
' A ' text,
PRIMARY KEY (' id '),
KEY ' First_path ' (' First_path ' (255)),
KEY ' dir ' (' dir '),
KEY ' A ' (' a ' (255))
) Engine=innodb DEFAULT Charset=utf8 |
So key inside a (255) represents 255 characters (one UTF8 character for 3 bytes).

Look at the Federated index again:

[BIGHD] (root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (pn, First_path);
Query OK, 0 ROWS affected, 1 Warning (0.10 sec)
records:0 duplicates:0 warnings:1

[BIGHD] (root@localhost) [cm]>
[BIGHD] (root@localhost) [cm]> 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)

[BIGHD] (root@localhost) [cm]> show CREATE TABLE temp_2;
| temp_2 | CREATE TABLE ' temp_2 ' (
' ID ' BIGINT UNSIGNED not NULL auto_increment,
' PN ' VARCHAR (128) DEFAULT ',
' First_path ' VARCHAR (256) DEFAULT ',
' dir ' VARCHAR (255) DEFAULT ',
' A ' text,
PRIMARY KEY (' id '),
KEY ' First_path ' (' First_path ' (255)),
KEY ' dir ' (' dir '),
KEY ' A ' (' a ' (255)),
KEY ' pn ' (' pn ', ' first_path ' (255))
) Engine=innodb DEFAULT Charset=utf8 |


Even for federated indexes, the 767 limit is also for fields, not the total length of the federated Index.

CREATE TABLE ' temp_2 ' (
' id ' BIGINT () UNSIGNED not NULL auto_increment,
  ' A ' text,
  ' B ' text,
  ' C ' text,
  ' d ' text,
  ' e ' text,
  PRIMARY KEY (' id ')
Engine=innodb DEFAULT Charset=utf8
 
 
255 * 3 = 765 * 4 = 3060  + 4*3 = 3072
[BIGHD] (root@localhost) [cm]> A Lter TABLE temp_2 ADD KEY (A (255), B (255), C (255), D (255), E (4));
Query OK, 0 ROWS affected (0.07 sec)
records:0  duplicates:0  warnings:0
 
[BIGHD] (root @localhost) [cm]>
[BIGHD] (root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (A (255), B (255), C (255), D (255), E (5) );
ERROR 1071 (42000): Specified KEY was too long; MAX KEY LENGTH is 3072 bytes

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.