今天有开发反应他的建表语句错误,我看了下,提示:
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 nullauto_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.00sec)
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.00sec)
root@test 03:55:05>select 767/2;
+----------+
| 767/2 |
+----------+
| 383.5000 |
+----------+
1 row in set (0.00sec)
root@test 03:55:47>create tablelingluo_2 (
-> id int(20) not nullauto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(383))
->)
-> COLLATE='gbk_chinese_ci'
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02sec)
root@test 03:55:53>create tablelingluo_3 (
-> id int(20) not nullauto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(383))
->)
-> charset=utf8
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.00sec)
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 tablelingluo_6 (
-> id int(20) not nullauto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(768))
->)
-> charset=latin1
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01sec)
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.00sec)
root@test 04:02:15>create tablelingluo_7 (
-> id int(20) not nullauto_increment,
-> aaa text,
-> primary key(id),
-> index idx_aaa(aaa(767))
->)
-> charset=latin1
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.01sec)
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.01sec)
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 tablelingluo_10 (
-> id int(20) not nullauto_increment,
-> aaa varchar(383),
-> primary key(id),
-> indexidx_aaa(aaa)
->)
-> charset=gbk
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.01sec)
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
转自
MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length - sunss - 博客园 https://www.cnblogs.com/sunss/archive/2012/05/17/2506396.html
mysql #1170错误(42000) BLOB/TEXT Column Used in Key Specification Without a Key Length - Thinkblog - CSDN博客 https://blog.csdn.net/BossDarcy/article/details/6209685