[mysql] Text上的索引长度是767字节 - Error 1170 (42000)的含义

博客首页 » mysql Text上的索引长度是767字节 - Error 1170 (42000)的含义

发布于 10 Sep 2015 07:18
标签 blog
MySQL报这个错误 Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length

http://www.cnblogs.com/sunss/archive/2012/05/17/2506396.html

今天有开发反应他的建表语句错误,我看了下,提示:

MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length

原因是:

MySQL不允许在BLOB/TEXT,TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, LONGTEXT,VARCHAR建索引,因为前面那些列类型都是可变长的,MySQL无法保证列的唯一性,只能在BLOB/TEXT前n个字节上建索引,这个n最大多长呢?做个测试:

复制代码
root@test 03:53:58>create table lingluo_1 ( -> id int(20) not null auto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(399))
-> )
-> COLLATE='gbk_chinese_ci'
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@test 03:54:58>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)

root@test 03:55:05>select 767/2;
+--+
| 767/2 |
+--+
| 383.5000 |
+--+
1 row in set (0.00 sec)
root@test 03:55:47>create table lingluo_2 (
-> id int(20) not null auto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(383))
-> )
-> COLLATE='gbk_chinese_ci'
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
root@test 03:55:53>create table lingluo_3 (
-> id int(20) not null auto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(383))
-> )
-> charset=utf8
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@test 03:58:08>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)

root@test 03:58:17>select 767/3;
+--+
| 767/3 |
+--+
| 255.6667 |
+--+
1 row in set (0.00 sec)

root@test 03:58:27>create table lingluo_4 (
-> id int(20) not null auto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(255))
-> )
-> charset=utf8
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

root@test 03:59:04>create table lingluo_5 (
-> id int(20) not null auto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(256))
-> )
-> charset=utf8
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@test 03:59:17>
root@test 03:59:17>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)
复制代码
对于gbk(一个汉字占两个字节)编码的字段,只能前383个字符建索引;对于utf8(一个汉字占三个字节)编码的字段,只能前255个字符建索引;对于latin编码的字段,只能前767个字符建索引;

复制代码
root@test 03:59:22>create table lingluo_6 (
-> id int(20) not null auto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(768))
-> )
-> charset=latin1
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@test 04:02:08>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)

root@test 04:02:15>create table lingluo_7 (
-> id int(20) not null auto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(767))
-> )
-> charset=latin1
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
root@test 04:32:39>create table lingluo_8 ( id int(20) not null auto_increment, aaa varchar(10000), primary key(id), index idx_aaa(aaa) ) charset=latin1 ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@test 04:32:46>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)
复制代码
同样的,当一个表里原来有非TEXT或者非BLOB字段(这些字段上有唯一索引或者普通索引)变为BLOB或TEXT的时候,也会遇到标题上的错误,如:

复制代码
root@test 04:44:15>create table lingluo_10 (
-> id int(20) not null auto_increment,
-> aaa varchar(383),
-> primary key(id),
-> index idx_aaa(aaa)
-> )
-> charset=gbk
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

root@test 04:44:39>alter table lingluo_10 modify aaa text;
ERROR 1170 (42000): BLOB/TEXT column 'aaa' used in key specification without a key length


本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • mysql Text上的索引长度是767字节 - Error 1170 (42000)的含义

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment