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]> CREATE TABLE `temp_2` ( 
2.     ->   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 
3.     ->   `pn` VARCHAR(128) DEFAULT '', 
4.     ->   `first_path` VARCHAR(256) DEFAULT '', 
5.     ->   `dir` VARCHAR(255) DEFAULT '', 
6.     -> `a` text, 
7.     ->   PRIMARY KEY (`id`) 
8.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
9.     ->  
10.     -> ; 
11. Query OK, 0 ROWS affected (0.15 sec) 
12.  
13. [BIGHD](root@localhost) [cm]>  
14. [BIGHD](root@localhost) [cm]>  
15. [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (first_path); 
16. Query OK, 0 ROWS affected, 1 warning (0.18 sec) 
17. Records: 0  Duplicates: 0  Warnings: 1 
18.  
19. [BIGHD](root@localhost) [cm]> SHOW warnings; 
20. +---------+------+---------------------------------------------------------+ 
21. | Level   | Code | Message                                                 | 
22. +---------+------+---------------------------------------------------------+ 
23. | Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes | 
24. +---------+------+---------------------------------------------------------+ 
25. 1 ROW IN SET (0.00 sec) 
26.  
27. [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (dir); 
28. Query OK, 0 ROWS affected (0.12 sec) 
29. Records: 0  Duplicates: 0  Warnings: 0 
30.  
31. [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (a(767)); 
32. Query OK, 0 ROWS affected, 1 warning (0.32 sec) 
33. Records: 0  Duplicates: 0  Warnings: 1 
34.  
35. [BIGHD](root@localhost) [cm]> SHOW warnings; 
36. +---------+------+---------------------------------------------------------+ 
37. | Level   | Code | Message                                                 | 
38. +---------+------+---------------------------------------------------------+ 
39. | Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes | 
40. +---------+------+---------------------------------------------------------+ 
41. 1 ROW IN SET (0.00 sec) 
42.  
43. [BIGHD](root@localhost) [cm]> SHOW CREATE TABLE temp_2; 
44. | temp_2 | CREATE TABLE `temp_2` ( 
45.   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 
46.   `pn` VARCHAR(128) DEFAULT '', 
47.   `first_path` VARCHAR(256) DEFAULT '', 
48.   `dir` VARCHAR(255) DEFAULT '', 
49.   `a` text, 
50.   PRIMARY KEY (`id`), 
51.   KEY `first_path` (`first_path`(255)), 
52.   KEY `dir` (`dir`), 
53.   KEY `a` (`a`(255)) 
54. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  

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

1. [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (pn, first_path); 
2. Query OK, 0 ROWS affected, 1 warning (0.10 sec) 
3. Records: 0  Duplicates: 0  Warnings: 1 
4.  
5. [BIGHD](root@localhost) [cm]>  
6. [BIGHD](root@localhost) [cm]> SHOW warnings; 
7. +---------+------+---------------------------------------------------------+ 
8. | Level   | Code | Message                                                 | 
9. +---------+------+---------------------------------------------------------+ 
10. | Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes | 
11. +---------+------+---------------------------------------------------------+ 
12. 1 ROW IN SET (0.00 sec) 
13.  
14. [BIGHD](root@localhost) [cm]> SHOW CREATE TABLE temp_2; 
15. | temp_2 | CREATE TABLE `temp_2` ( 
16.   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 
17.   `pn` VARCHAR(128) DEFAULT '', 
18.   `first_path` VARCHAR(256) DEFAULT '', 
19.   `dir` VARCHAR(255) DEFAULT '', 
20.   `a` text, 
21.   PRIMARY KEY (`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=InnoDB DEFAULT CHARSET=utf8 | 

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

1. CREATE TABLE `temp_2` ( 
2. `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 
3.   `a` text, 
4.   `b` text, 
5.   `c` text, 
6.   `d` text, 
7.   `e` text, 
8.   PRIMARY KEY (`id`) 
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
10.  
11.  
12. 255 * 3 = 765 * 4 = 3060  + 4*3 = 3072 
13. [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(4)); 
14. Query OK, 0 ROWS affected (0.07 sec) 
15. Records: 0  Duplicates: 0  Warnings: 0 
16.  //phpfensi.com
17. [BIGHD](root@localhost) [cm]>  
18. [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(5)); 
19. ERROR 1071 (42000): Specified KEY was too long; MAX KEY LENGTH IS 3072 bytes