問(wèn)題描述
當(dāng)我執(zhí)行以下命令時(shí):
ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);
我收到此錯(cuò)誤消息:
#1071 - Specified key was too long; max key length is 767 bytes
關(guān)于 column1 和 column2 的信息:
Information about column1 and column2:
column1 varchar(20) utf8_general_ci
column2 varchar(500) utf8_general_ci
我認(rèn)為 varchar(20) 只需要 21 個(gè)字節(jié)，而 varchar(500) 只需要 501 個(gè)字節(jié).所以總字節(jié)數(shù)是 522，小于 767.那么為什么我會(huì)收到錯(cuò)誤消息?
I think varchar(20) only requires 21 bytes while varchar(500) only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message?
#1071 - Specified key was too long; max key length is 767 bytes
推薦答案
767 字節(jié)是 規(guī)定的前綴限制在 MySQL 5.6 版(和之前的版本)中的 InnoDB 表.MyISAM 表的長(zhǎng)度為 1,000 字節(jié).在 MySQL 5.7 及更高版本中，此限制已增加到 3072 字節(jié).
767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.
您還必須注意，如果您在 utf8mb4 編碼的 big char 或 varchar 字段上設(shè)置索引，則必須將 767 字節(jié)(或 3072 字節(jié))的最大索引前綴長(zhǎng)度除以 4，結(jié)果為 191.這是因?yàn)?utf8mb4 字符的最大長(zhǎng)度是四個(gè)字節(jié).對(duì)于 utf8 字符，它將是三個(gè)字節(jié)，導(dǎo)致最大索引前綴長(zhǎng)度為 254.
You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 254.
您可以選擇的一種方法是為您的 VARCHAR 字段設(shè)置下限.
One option you have is to just place lower limit on your VARCHAR fields.
另一種選擇(根據(jù)對(duì)此問(wèn)題的回應(yīng))是獲得列的子集而不是整個(gè)金額，即:
Another option (according to the response to this issue) is to get the subset of the column rather than the entire amount, i.e.:
ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );
調(diào)整，因?yàn)槟枰@取要應(yīng)用的密鑰，但我想知道是否值得檢查有關(guān)此實(shí)體的數(shù)據(jù)模型，以查看是否有改進(jìn)可以讓您在不影響 MySQL 的情況下實(shí)現(xiàn)預(yù)期的業(yè)務(wù)規(guī)則限制.
Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements that would allow you to implement the intended business rules without hitting the MySQL limitation.
這篇關(guān)于MySQL 錯(cuò)誤 #1071 - 指定的鍵太長(zhǎng)；最大密鑰長(zhǎng)度為 767 字節(jié)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！