夏日博客

A-A+

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

2017年10月24日 PHP技术文章 暂无评论 阅读 0 views 次

在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. 255 * 3 = 765 * 4 = 3060  + 4*3 = 3072   
  12. [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(4));   
  13. Query OK, 0 ROWS affected (0.07 sec)   
  14. Records: 0  Duplicates: 0  Warnings: 0   
  15.  //www.xiariboke.net  
  16. [BIGHD](root@localhost) [cm]>    
  17. [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(5));   
  18. ERROR 1071 (42000): Specified KEY was too long; MAX KEY LENGTH IS 3072 bytes  
标签：

给我留言