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

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

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

 

转自

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

转载于:https://www.cnblogs.com/paul8339/p/11174052.html