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