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