用户名:
密 码: 记住
您当前的位置:首页 > 网络编程 > mysql教程

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

时间:2015-01-23  来源:西部数据  作者:西部数据

在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
来顶一下
返回首页
返回首页
推荐资讯
WiFi太不安全:7岁女孩11分钟内入侵公共网络 WiFi太不安全:7岁女孩11分钟内入侵近期刚刚发布研究说WiFi网络能获得人们手机里多少私人信息,
不服跑个分?人工智能也出现“刷分”乱象 不服跑个分?人工智能也出现“刷分2014年,人工智能领域突然爆发,成为了科研和科技创业的热门
相关文章
栏目更新
栏目热门