程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql中innodb utf8字符集varchar索引長度問題

mysql中innodb utf8字符集varchar索引長度問題

編輯:MySQL綜合教程

在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。

[BIGHD](root@localhost) [cm]> CREATE TABLE `temp_2` (
    ->   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `pn` VARCHAR(128) DEFAULT '',
    ->   `first_path` VARCHAR(256) DEFAULT '',
    ->   `dir` VARCHAR(255) DEFAULT '',
    -> `a` text,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    ->
    -> ;
Query OK, 0 ROWS affected (0.15 sec)
 
[BIGHD](root@localhost) [cm]>
[BIGHD](root@localhost) [cm]>
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (first_path);
Query OK, 0 ROWS affected, 1 warning (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
[BIGHD](root@localhost) [cm]> SHOW warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes |
+---------+------+---------------------------------------------------------+
1 ROW IN SET (0.00 sec)
 
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (dir);
Query OK, 0 ROWS affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (a(767));
Query OK, 0 ROWS affected, 1 warning (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
[BIGHD](root@localhost) [cm]> SHOW warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes |
+---------+------+---------------------------------------------------------+
1 ROW IN SET (0.00 sec)
 
[BIGHD](root@localhost) [cm]> SHOW CREATE TABLE temp_2;
| temp_2 | CREATE TABLE `temp_2` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pn` VARCHAR(128) DEFAULT '',
  `first_path` VARCHAR(256) DEFAULT '',
  `dir` VARCHAR(255) DEFAULT '',
  `a` text,
  PRIMARY KEY (`id`),
  KEY `first_path` (`first_path`(255)),
  KEY `dir` (`dir`),
  KEY `a` (`a`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
所以key裡面a(255),表示 255個字符(一個utf8字符占3字節)。

再看一下聯合索引的情況:

[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (pn, first_path);
Query OK, 0 ROWS affected, 1 warning (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
[BIGHD](root@localhost) [cm]>
[BIGHD](root@localhost) [cm]> SHOW warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes |
+---------+------+---------------------------------------------------------+
1 ROW IN SET (0.00 sec)
 
[BIGHD](root@localhost) [cm]> SHOW CREATE TABLE temp_2;
| temp_2 | CREATE TABLE `temp_2` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pn` VARCHAR(128) DEFAULT '',
  `first_path` VARCHAR(256) DEFAULT '',
  `dir` VARCHAR(255) DEFAULT '',
  `a` text,
  PRIMARY KEY (`id`),
  KEY `first_path` (`first_path`(255)),
  KEY `dir` (`dir`),
  KEY `a` (`a`(255)),
  KEY `pn` (`pn`,`first_path`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


即使是聯合索引,767的限制也是針對字段的,而不是聯合索引的總長度。

CREATE TABLE `temp_2` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `a` text,
  `b` text,
  `c` text,
  `d` text,
  `e` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
255 * 3 = 765 * 4 = 3060  + 4*3 = 3072
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(4));
Query OK, 0 ROWS affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
[BIGHD](root@localhost) [cm]>
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(5));
ERROR 1071 (42000): Specified KEY was too long; MAX KEY LENGTH IS 3072 bytes

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved