CREATE TABLE fails
在创建MySQL表时,可能会遇到"CREATE TABLE fails"的问题。这个问题的出现是因为在定义索引时没有明确指定要索引的TEXT
列的前导部分。
InnoDB
引擎对于每个索引键有一个768
字节的限制,超过这个限制将无法创建索引。解决方法是在创建表时,明确指定要索引的TEXT
列的长度。
以下是一个示例的解决方法:
CREATE TABLE t_length ( mydata TEXT NOT NULL, KEY ix_length_mydata (mydata(255))) ENGINE=InnoDB;
需要注意的是,索引键大小的最大值取决于列的字符集。对于单字节字符集(比如LATIN1
),最大值是767
个字符;而对于UTF8
字符集,最大值是255
个字符(因为MySQL
仅使用最多3
个字节表示的BMP
字符)。
如果需要将整个列作为主键,可以计算SHA1
或MD5
哈希值,并将其用作主键。
关于字符数和字节数的问题,stackoverflow.com/questions/15157227/…给出了解释,其中明确说明了255指的是字符数,而不是字节数。这样做是为了在UTF-8字符集下能够适应旧的768字节限制。
在这个问题的回答中,还提到了在使用REDUNDANT
或COMPACT
行格式的InnoDB表中,索引键前缀长度限制为767字节。例如,在TEXT
或VARCHAR
列上使用超过255个字符的列前缀索引时,假设使用utf8mb3字符集,并且每个字符最多占用3个字节,可能会达到这个限制。
MySQL错误1170(42000):未在键规范中使用键长度的BLOB/TEXT列
MySQL的索引仅能索引BLOB或TEXT列的前N个字符。因此,当存在TEXT或BLOB字段/列类型,或者这些字段/列属于TEXT或BLOB类型,如TINYBLOB、MEDIUMBLOB、LONGBLOB、TINYTEXT、MEDIUMTEXT和LONGTEXT时,尝试将其作为主键或索引时会发生错误。由于BLOB或TEXT类型的大小是可变的和动态的,MySQL无法保证列的唯一性。因此,当使用BLOB或TEXT类型作为索引时,必须提供N的值,以便MySQL可以确定键的长度。然而,MySQL不支持对TEXT或BLOB设置键长度限制。因此,TEXT(88)是无效的。
当尝试将表列从非TEXT和非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。解决此问题的方法是为VARCHAR字段指定小于256的大小。
参考:MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
dev.mysql.com/doc/refman/5.0/en/char.html "VARCHAR列中的值是可变长度字符串。在MySQL 5.0.3之前,长度可以指定为0到255的值,在5.0.3及更高版本中,长度可以指定为0到65,535。在MySQL 5.0.3及更高版本中,VARCHAR的有效最大长度取决于最大行大小(65,535字节,该大小在所有列之间共享)"
在你说“MySQL仅能索引BLOB或TEXT列的前N个字符”时,N的值是多少?
“在对BLOB或TEXT列进行索引时,必须为索引指定前缀长度。”dev.mysql.com/doc/refman/5.6/en/column-indexes.html
我知道有点晚了,但是移除唯一键约束解决了问题。我没有将TEXT列用作PK,但我尝试将其设置为唯一。我得到了1170错误,但是当我移除它时,错误消失了。
很好的答案。以下是懒惰的Python和SQLAlchemy用户的示例代码:
from sqlalchemy.types import Date, VARCHAR df.to_sql(table_name, con=engine, index=index, if_exists='append', index_label=index_label, dtype={'date': Date, 'text': VARCHAR(25)})
似乎末尾的链接已损坏。
在我的编辑被批准之前,这是一个修复的链接。