mysql中innodb utf8字符集varchar索引长度问题

在mysql中innodb为uft8字符集时发现text,varchar这些字符索引会无效了，因类innodb utf8字符集下索引的长度限制单一字段的索引长度限制为767 bytes

索引总长度的限制是:The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)

在utf8字符集下,varchar(255) 的[数据部分]占用 255*3=765 bytes,最接近767bytes,256*3 = 768bytes,已经超过767.

1. [BIGHD](root@localhost)[cm]>CREATETABLE`temp_2`(
2. ->`id`BIGINT(20)UNSIGNEDNOTNULLAUTO_INCREMENT,
3. ->`pn`VARCHAR(128)DEFAULT'',
4. ->`first_path`VARCHAR(256)DEFAULT'',
5. ->`dir`VARCHAR(255)DEFAULT'',
6. ->`a`text,
7. ->PRIMARYKEY(`id`)
8. ->)ENGINE=InnoDBDEFAULTCHARSET=utf8
9. ->
10. ->;
11. QueryOK,0ROWSaffected(0.15sec)
13. [BIGHD](root@localhost)[cm]>
14. [BIGHD](root@localhost)[cm]>
15. [BIGHD](root@localhost)[cm]>ALTERTABLEtemp_2ADDKEY(first_path);
16. QueryOK,0ROWSaffected,1warning(0.18sec)
17. Records:0Duplicates:0Warnings:1
19. [BIGHD](root@localhost)[cm]>SHOWwarnings;
20. +---------+------+---------------------------------------------------------+
21. |Level|Code|Message|
22. +---------+------+---------------------------------------------------------+
23. |Warning|1071|SpecifiedKEYwastoolong;MAXKEYLENGTHIS767bytes|
24. +---------+------+---------------------------------------------------------+
25. 1ROWINSET(0.00sec)
27. [BIGHD](root@localhost)[cm]>ALTERTABLEtemp_2ADDKEY(dir);
28. QueryOK,0ROWSaffected(0.12sec)
29. Records:0Duplicates:0Warnings:0
31. [BIGHD](root@localhost)[cm]>ALTERTABLEtemp_2ADDKEY(a(767));
32. QueryOK,0ROWSaffected,1warning(0.32sec)
33. Records:0Duplicates:0Warnings:1
35. [BIGHD](root@localhost)[cm]>SHOWwarnings;
36. +---------+------+---------------------------------------------------------+
37. |Level|Code|Message|
38. +---------+------+---------------------------------------------------------+
39. |Warning|1071|SpecifiedKEYwastoolong;MAXKEYLENGTHIS767bytes|
40. +---------+------+---------------------------------------------------------+
41. 1ROWINSET(0.00sec)
43. [BIGHD](root@localhost)[cm]>SHOWCREATETABLEtemp_2;
44. |temp_2|CREATETABLE`temp_2`(
45. `id`BIGINT(20)UNSIGNEDNOTNULLAUTO_INCREMENT,
46. `pn`VARCHAR(128)DEFAULT'',
47. `first_path`VARCHAR(256)DEFAULT'',
48. `dir`VARCHAR(255)DEFAULT'',
49. `a`text,
50. PRIMARYKEY(`id`),
51. KEY`first_path`(`first_path`(255)),
52. KEY`dir`(`dir`),
53. KEY`a`(`a`(255))
54. )ENGINE=InnoDBDEFAULTCHARSET=utf8|

所以key里面a(255),表示 255个字符(一个utf8字符占3字节),再看一下联合索引的情况:

1. [BIGHD](root@localhost)[cm]>ALTERTABLEtemp_2ADDKEY(pn,first_path);
2. QueryOK,0ROWSaffected,1warning(0.10sec)
3. Records:0Duplicates:0Warnings:1
5. [BIGHD](root@localhost)[cm]>
6. [BIGHD](root@localhost)[cm]>SHOWwarnings;
7. +---------+------+---------------------------------------------------------+
8. |Level|Code|Message|
9. +---------+------+---------------------------------------------------------+
10. |Warning|1071|SpecifiedKEYwastoolong;MAXKEYLENGTHIS767bytes|
11. +---------+------+---------------------------------------------------------+
12. 1ROWINSET(0.00sec)
14. [BIGHD](root@localhost)[cm]>SHOWCREATETABLEtemp_2;
15. |temp_2|CREATETABLE`temp_2`(
16. `id`BIGINT(20)UNSIGNEDNOTNULLAUTO_INCREMENT,
17. `pn`VARCHAR(128)DEFAULT'',
18. `first_path`VARCHAR(256)DEFAULT'',
19. `dir`VARCHAR(255)DEFAULT'',
20. `a`text,
21. PRIMARYKEY(`id`),
22. KEY`first_path`(`first_path`(255)),
23. KEY`dir`(`dir`),
24. KEY`a`(`a`(255)),
25. KEY`pn`(`pn`,`first_path`(255))
26. )ENGINE=InnoDBDEFAULTCHARSET=utf8|

即使是联合索引,767的限制也是针对字段的,而不是联合索引的总长度,代码如下:

1. CREATETABLE`temp_2`(
2. `id`BIGINT(20)UNSIGNEDNOTNULLAUTO_INCREMENT,
3. `a`text,
4. `b`text,
5. `c`text,
6. `d`text,
7. `e`text,
8. PRIMARYKEY(`id`)
9. )ENGINE=InnoDBDEFAULTCHARSET=utf8
12. 255*3=765*4=3060+4*3=3072
13. [BIGHD](root@localhost)[cm]>ALTERTABLEtemp_2ADDKEY(a(255),b(255),c(255),d(255),e(4));
14. QueryOK,0ROWSaffected(0.07sec)
15. Records:0Duplicates:0Warnings:0
16. //phpfensi.com
17. [BIGHD](root@localhost)[cm]>
18. [BIGHD](root@localhost)[cm]>ALTERTABLEtemp_2ADDKEY(a(255),b(255),c(255),d(255),e(5));
19. ERROR1071(42000):SpecifiedKEYwastoolong;MAXKEYLENGTHIS3072bytes