MySQL Index length limit issue

Source: Internet
Author: User
Tags mysql index

An error occurred while modifying the table structure: Specified key was too long;max key length is bytes.

MySQL version for server version:5.1.36, Execute SQL for:

ALTER TABLE pre_common_diy_data Modify column Targettplname varchar (255);

If a character is calculated as two bytes and the 2*255=510 is not more than the first character, how can it be an error?

After querying the relevant data, it is found that the MySQL MyIsam storage Engine when the index is created, the index key length is a strict length limit, all the index key maximum length sum cannot exceed the sum of theactual data length, Instead, the index key field defines the sum of the lengths.

The main character set is calculated as follows:

Latin1 = 1 byte = 1 character

Uft8 = 3 byte = 1 character

GBK = 2 byte = 1 character

1, view the MySQL storage engine, the default storage engine is MyISAM.

Mysql> show engines;

+------------+---------+-----------------------------------------------------------+--------------+------+----- -------+

| Engine | Support | Comment | Transactions | XA | savepoints |

+------------+---------+-----------------------------------------------------------+--------------+------+----- -------+

| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV Storage Engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

+------------+---------+-----------------------------------------------------------+--------------+------+----- -------+

4 rows in Set (0.00 sec)

2, we view the MySQL table structure, the total index length is: (100+80) =180

mysql> desc Pre_common_diy_data;

+---------------+-----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------------+-----------------------+------+-----+---------+-------+

| Targettplname | varchar (100) | NO |         PRI |       | |

| Tpldirectory | varchar (80) | NO |         PRI |       | |

| Primaltplname | varchar (255) |     NO |         |       | |

| diycontent | Mediumtext |     NO | |       NULL | |

| name | varchar (255) |     NO |         |       | |

| UID | Mediumint (8) unsigned |     NO | |       0 | |

| Username | varchar (15) |     NO |         |       | |

| Dateline | Int (Ten) unsigned |     NO | |       0 | |

+---------------+-----------------------+------+-----+---------+-------+

8 rows in Set (0.00 sec)

3.view the character set of the table as UTF8 characters, then the total index length is: 180*3=480

Mysql> Show CREATE TABLE pre_common_diy_data;

+---------------------+------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------+

| table               |  create table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |

+---------------------+---------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+

| Pre_common_diy_data | CREATE TABLE ' Pre_common_diy_data ' (

' Targettplname ' varchar (+) not NULL DEFAULT ' ',

' tpldirectory ' varchar (+) not NULL DEFAULT ' ',

' Primaltplname ' varchar (255) Not NULL DEFAULT ' ',

' Diycontent ' Mediumtext not NULL,

' Name ' varchar (255) Not NULL DEFAULT ' ',

' UID ' mediumint (8) unsigned not NULL DEFAULT ' 0 ',

' username ' varchar (+) not NULL DEFAULT ' ',

' Dateline ' int (ten) unsigned not NULL DEFAULT ' 0 ',

PRIMARY KEY (' targettplname ', ' tpldirectory ')

) Engine=myisam DEFAULT Charset=utf8 |

+---------------------+---------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+

1 row in Set (0.00 sec)

Then check out our execution error SQL:

ALTER TABLE pre_common_diy_data Modify column Targettplname varchar (255);

Calculated total length: (80+255) *3=1005, has exceeded the limit, so error.

The workaround is to reduce the length of the field:

ALTER TABLE pre_common_diy_data Modify column Targettplname varchar (240);

Successful execution.

In addition , there is an index key length limit for the combination index that creates innodb.

MySQL Index length limit issue

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.