我有一个主键是一个varchar(255)的表 . 出现了一些情况,其中255个字符是不够的 . 我尝试将字段更改为文本,但是我收到以下错误:
BLOB/TEXT column 'message_id' used in key specification without a key length
我怎样才能解决这个问题?
编辑:我还应该指出这个表有一个包含多列的复合主键 .
此外,如果要在此字段中使用索引,则应使用MyISAM存储引擎和FULLTEXT索引类型 .
alter table authors ADD UNIQUE(name_first(767), name_second(767));
NOTE : 767 是MySQL在处理blob /文本索引时索引列的字符数限制
参考:http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
像这样使用
@Id @Column(name = "userEmailId", length=100) private String userEmailId;
不要将长值作为主键 . 这会破坏你的表现 . 请参阅mysql手册,第13.6.13节“InnoDB性能调整和故障排除” .
相反,将一个代理int键作为主键(使用auto_increment),将您的loong键作为辅助UNIQUE .
您可以在alter table请求中指定密钥长度,例如:
alter table authors ADD UNIQUE(name_first(20), name_second(20));
发生错误是因为MySQL只能索引BLOB或 TEXT 列的前N个字符 . 因此,当存在 TEXT 或BLOB的字段/列类型或属于 TINYBLOB 或 BLOB 类型(例如 TINYBLOB , MEDIUMBLOB , LONGBLOB , TINYTEXT , MEDIUMTEXT 和 LONGTEXT )时,您尝试创建主键或索引时,会发生错误 . 对于没有长度值的完整 BLOB 或 TEXT ,MySQL无法保证列的唯一性,因为它具有可变和动态大小 . 因此,当使用 BLOB 或 TEXT 类型作为索引时,必须提供N的值,以便MySQL可以确定密钥长度 . 但是,MySQL不支持 TEXT 或 BLOB 的密钥长度限制 . TEXT(88) 根本行不通 .
TEXT
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
MEDIUMTEXT
LONGTEXT
TEXT(88)
当您尝试将 non-TEXT 和 non-BLOB 类型的表列(例如 VARCHAR 和 ENUM )转换为 TEXT 或 BLOB 类型时,也会弹出错误,该列已被定义为唯一约束或索引 . Alter Table SQL命令将失败 .
non-TEXT
non-BLOB
VARCHAR
ENUM
该问题的解决方案是从索引或唯一约束中删除 TEXT 或 BLOB 列,或将另一个字段设置为主键 . 如果您不能这样做,并希望对 TEXT 或 BLOB 列设置限制,请尝试使用 VARCHAR 类型并对其设置长度限制 . 默认情况下, VARCHAR 限制为最多255个字符,并且必须在声明后立即在括号内隐式指定其限制,即 VARCHAR(200) 仅将其限制为200个字符 .
VARCHAR(200)
有时,即使您不在表中使用 TEXT 或 BLOB 相关类型,也可能出现错误1170 . 它发生在诸如将 VARCHAR 列指定为主键但错误地设置其长度或字符大小的情况下 . VARCHAR 最多只能接受256个字符,因此 VARCHAR(512) 之类的任何内容都会强制MySQL将 VARCHAR(512) 自动转换为 SMALLTEXT 数据类型,如果列用作主键或唯一或非列,则数据类型随后会因密钥长度错误1170而失败独特的指数 . 要解决此问题,请指定小于256的数字作为 VARCHAR 字段的大小 .
VARCHAR(512)
SMALLTEXT
参考:MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
该问题的解决方案是,在 CREATE TABLE 语句中,您可以在列创建定义之后添加约束 UNIQUE ( problemtextfield(300) ) ,以指定 TEXT 字段的 key 长度 300 字符 . 然后 problemtextfield TEXT 字段的第一个 300 字符需要是唯一的,之后的任何差异都将被忽略 .
CREATE TABLE
UNIQUE ( problemtextfield(300) )
key
300
problemtextfield
您必须将列类型更改为 varchar 或 integer 才能进行索引 .
varchar
integer
处理此问题的另一个好方法是创建没有唯一约束的TEXT字段,并添加一个唯一的兄弟VARCHAR字段,该字段包含TEXT字段的摘要(MD5,SHA1等) . 在插入或更新TEXT字段时,在整个TEXT字段上计算并存储摘要,然后在整个TEXT字段(而不是某些前导部分)上具有可以快速搜索的唯一性约束 .
MySQL不允许索引 BLOB , TEXT 和long VARCHAR 列的完整值,因为它们包含的数据可能很大,并且隐式地DB索引会很大,这意味着索引没有任何好处 .
MySQL要求你定义要编入索引的前N个字符,诀窍是选择一个足够长的数字N来提供良好的选择性,但要足够短以节省空间 . 前缀应足够长,以使索引几乎与您索引整列时的索引一样有用 .
在我们进一步讨论之前,让我们定义一些重要术语 Index selectivity 是不同的索引值总数与总行数之比 . 以下是测试表的一个示例:
+-----+-----------+ | id | value | +-----+-----------+ | 1 | abc | | 2 | abd | | 3 | adg | +-----+-----------+
如果我们只索引第一个字符(N = 1),那么索引表将会显示如下表:
+---------------+-----------+ | indexedValue | rows | +---------------+-----------+ | a | 1,2,3 | +---------------+-----------+
在这种情况下,指数选择性等于IS = 1/3 = 0.33 .
现在让我们看看如果我们将索引字符数增加到两个(N = 2)会发生什么 .
+---------------+-----------+ | indexedValue | rows | +---------------+-----------+ | ab | 1,2 | | ad | 3 | +---------------+-----------+
在这种情况下,IS = 2/3 = 0.66这意味着我们增加了索引选择性,但我们也增加了索引的大小 . 特技是找到最小数量N,这将导致最大索引选择性 .
您可以使用两种方法为数据库表进行计算 . 我将在this database dump进行演示 .
假设我们想要将表employee中的列last_name添加到索引中,并且我们想要定义将产生最佳索引选择性的最小数量N.
首先让我们确定最常见的姓氏:
select count(*) as cnt, last_name from employees group by employees.last_name order by cnt +-----+-------------+ | cnt | last_name | +-----+-------------+ | 226 | Baba | | 223 | Coorg | | 223 | Gelosh | | 222 | Farris | | 222 | Sudbeck | | 221 | Adachi | | 220 | Osgood | | 218 | Neiman | | 218 | Mandell | | 218 | Masada | | 217 | Boudaillier | | 217 | Wendorf | | 216 | Pettis | | 216 | Solares | | 216 | Mahnke | +-----+-------------+ 15 rows in set (0.64 sec)
如您所见,Baba的姓氏是最常见的名字 . 现在我们将找到最常出现的last_name前缀,以五个字母的前缀开头 .
+-----+--------+ | cnt | prefix | +-----+--------+ | 794 | Schaa | | 758 | Mande | | 711 | Schwa | | 562 | Angel | | 561 | Gecse | | 555 | Delgr | | 550 | Berna | | 547 | Peter | | 543 | Cappe | | 539 | Stran | | 534 | Canna | | 485 | Georg | | 417 | Neima | | 398 | Petti | | 398 | Duclo | +-----+--------+ 15 rows in set (0.55 sec)
每个前缀都会出现更多,这意味着我们必须增加数字N,直到值与前一个示例几乎相同 .
这是N = 9的结果
select count(*) as cnt, left(last_name,9) as prefix from employees group by prefix order by cnt desc limit 0,15; +-----+-----------+ | cnt | prefix | +-----+-----------+ | 336 | Schwartzb | | 226 | Baba | | 223 | Coorg | | 223 | Gelosh | | 222 | Sudbeck | | 222 | Farris | | 221 | Adachi | | 220 | Osgood | | 218 | Mandell | | 218 | Neiman | | 218 | Masada | | 217 | Wendorf | | 217 | Boudailli | | 216 | Cummings | | 216 | Pettis | +-----+-----------+
这是N = 10的结果 .
+-----+------------+ | cnt | prefix | +-----+------------+ | 226 | Baba | | 223 | Coorg | | 223 | Gelosh | | 222 | Sudbeck | | 222 | Farris | | 221 | Adachi | | 220 | Osgood | | 218 | Mandell | | 218 | Neiman | | 218 | Masada | | 217 | Wendorf | | 217 | Boudaillie | | 216 | Cummings | | 216 | Pettis | | 216 | Solares | +-----+------------+ 15 rows in set (0.56 sec)
这是非常好的结果 . 这意味着我们可以在列 last_name 上 Build 索引,仅索引前10个字符 . 在表定义列 last_name 被定义为 VARCHAR(16) ,这意味着我们已经为每个条目保存了6个字节(如果姓氏中有UTF8字符,则保存更多字节) . 在这个表中有1637个不同的值乘以6个字节大约9KB,并想象如果我们的表包含数百万行,这个数字将如何增长 .
last_name
VARCHAR(16)
您可以在我的帖子Prefixed indexes in MySQL中阅读其他计算N数的方法 .
添加另一个varChar(255)列(默认为空字符串非null)以在255个字符不足时保持溢出,并将此PK更改为使用这两个列 . 这听起来并不像一个设计良好的数据库模式,我建议让一个数据建模器来查看你所拥有的内容,以便重构它以实现更多规范化 .
转到mysql edit table - >将列类型更改为 varchar(45) .
edit table
varchar(45)
您应该定义要索引的 TEXT 列的哪个前导部分 .
InnoDB 对每个索引键有 768 个字节的限制,您将无法创建超过该索引的索引 .
InnoDB
768
这样可以正常工作:
CREATE TABLE t_length ( mydata TEXT NOT NULL, KEY ix_length_mydata (mydata(255))) ENGINE=InnoDB;
请注意,密钥大小的最大值取决于列charset . 对于 LATIN1 这样的单字节字符集, 767 字符为 UTF8 只有 255 字符( MySQL 仅使用 BMP ,每个字符最多需要 3 字节)
LATIN1
767
UTF8
255
MySQL
BMP
3
如果您需要将整列作为 PRIMARY KEY ,请计算 SHA1 或 MD5 哈希并将其用作 PRIMARY KEY .
PRIMARY KEY
SHA1
MD5
13 回答
此外,如果要在此字段中使用索引,则应使用MyISAM存储引擎和FULLTEXT索引类型 .
NOTE : 767 是MySQL在处理blob /文本索引时索引列的字符数限制
参考:http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
像这样使用
不要将长值作为主键 . 这会破坏你的表现 . 请参阅mysql手册,第13.6.13节“InnoDB性能调整和故障排除” .
相反,将一个代理int键作为主键(使用auto_increment),将您的loong键作为辅助UNIQUE .
您可以在alter table请求中指定密钥长度,例如:
发生错误是因为MySQL只能索引BLOB或
TEXT
列的前N个字符 . 因此,当存在TEXT
或BLOB的字段/列类型或属于TINYBLOB
或BLOB
类型(例如TINYBLOB
,MEDIUMBLOB
,LONGBLOB
,TINYTEXT
,MEDIUMTEXT
和LONGTEXT
)时,您尝试创建主键或索引时,会发生错误 . 对于没有长度值的完整BLOB
或TEXT
,MySQL无法保证列的唯一性,因为它具有可变和动态大小 . 因此,当使用BLOB
或TEXT
类型作为索引时,必须提供N的值,以便MySQL可以确定密钥长度 . 但是,MySQL不支持TEXT
或BLOB
的密钥长度限制 .TEXT(88)
根本行不通 .当您尝试将
non-TEXT
和non-BLOB
类型的表列(例如VARCHAR
和ENUM
)转换为TEXT
或BLOB
类型时,也会弹出错误,该列已被定义为唯一约束或索引 . Alter Table SQL命令将失败 .该问题的解决方案是从索引或唯一约束中删除
TEXT
或BLOB
列,或将另一个字段设置为主键 . 如果您不能这样做,并希望对TEXT
或BLOB
列设置限制,请尝试使用VARCHAR
类型并对其设置长度限制 . 默认情况下,VARCHAR
限制为最多255个字符,并且必须在声明后立即在括号内隐式指定其限制,即VARCHAR(200)
仅将其限制为200个字符 .有时,即使您不在表中使用
TEXT
或BLOB
相关类型,也可能出现错误1170 . 它发生在诸如将VARCHAR
列指定为主键但错误地设置其长度或字符大小的情况下 .VARCHAR
最多只能接受256个字符,因此VARCHAR(512)
之类的任何内容都会强制MySQL将VARCHAR(512)
自动转换为SMALLTEXT
数据类型,如果列用作主键或唯一或非列,则数据类型随后会因密钥长度错误1170而失败独特的指数 . 要解决此问题,请指定小于256的数字作为VARCHAR
字段的大小 .参考:MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
该问题的解决方案是,在
CREATE TABLE
语句中,您可以在列创建定义之后添加约束UNIQUE ( problemtextfield(300) )
,以指定TEXT
字段的key
长度300
字符 . 然后problemtextfield
TEXT
字段的第一个300
字符需要是唯一的,之后的任何差异都将被忽略 .您必须将列类型更改为
varchar
或integer
才能进行索引 .处理此问题的另一个好方法是创建没有唯一约束的TEXT字段,并添加一个唯一的兄弟VARCHAR字段,该字段包含TEXT字段的摘要(MD5,SHA1等) . 在插入或更新TEXT字段时,在整个TEXT字段上计算并存储摘要,然后在整个TEXT字段(而不是某些前导部分)上具有可以快速搜索的唯一性约束 .
MySQL不允许索引
BLOB
,TEXT
和longVARCHAR
列的完整值,因为它们包含的数据可能很大,并且隐式地DB索引会很大,这意味着索引没有任何好处 .MySQL要求你定义要编入索引的前N个字符,诀窍是选择一个足够长的数字N来提供良好的选择性,但要足够短以节省空间 . 前缀应足够长,以使索引几乎与您索引整列时的索引一样有用 .
在我们进一步讨论之前,让我们定义一些重要术语 Index selectivity 是不同的索引值总数与总行数之比 . 以下是测试表的一个示例:
如果我们只索引第一个字符(N = 1),那么索引表将会显示如下表:
在这种情况下,指数选择性等于IS = 1/3 = 0.33 .
现在让我们看看如果我们将索引字符数增加到两个(N = 2)会发生什么 .
在这种情况下,IS = 2/3 = 0.66这意味着我们增加了索引选择性,但我们也增加了索引的大小 . 特技是找到最小数量N,这将导致最大索引选择性 .
您可以使用两种方法为数据库表进行计算 . 我将在this database dump进行演示 .
假设我们想要将表employee中的列last_name添加到索引中,并且我们想要定义将产生最佳索引选择性的最小数量N.
首先让我们确定最常见的姓氏:
如您所见,Baba的姓氏是最常见的名字 . 现在我们将找到最常出现的last_name前缀,以五个字母的前缀开头 .
每个前缀都会出现更多,这意味着我们必须增加数字N,直到值与前一个示例几乎相同 .
这是N = 9的结果
这是N = 10的结果 .
这是非常好的结果 . 这意味着我们可以在列
last_name
上 Build 索引,仅索引前10个字符 . 在表定义列last_name
被定义为VARCHAR(16)
,这意味着我们已经为每个条目保存了6个字节(如果姓氏中有UTF8字符,则保存更多字节) . 在这个表中有1637个不同的值乘以6个字节大约9KB,并想象如果我们的表包含数百万行,这个数字将如何增长 .您可以在我的帖子Prefixed indexes in MySQL中阅读其他计算N数的方法 .
添加另一个varChar(255)列(默认为空字符串非null)以在255个字符不足时保持溢出,并将此PK更改为使用这两个列 . 这听起来并不像一个设计良好的数据库模式,我建议让一个数据建模器来查看你所拥有的内容,以便重构它以实现更多规范化 .
转到mysql
edit table
- >将列类型更改为varchar(45)
.您应该定义要索引的
TEXT
列的哪个前导部分 .InnoDB
对每个索引键有768
个字节的限制,您将无法创建超过该索引的索引 .这样可以正常工作:
请注意,密钥大小的最大值取决于列charset . 对于
LATIN1
这样的单字节字符集,767
字符为UTF8
只有255
字符(MySQL
仅使用BMP
,每个字符最多需要3
字节)如果您需要将整列作为
PRIMARY KEY
,请计算SHA1
或MD5
哈希并将其用作PRIMARY KEY
.