MySQL 密钥规范中使用的 BLOB/TEXT 列“bestilling”没有密钥长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13710170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BLOB/TEXT column 'bestilling' used in key specification without a key length
提问by Ismail
I am trying to make a order system, but I am stuck right now. In the mysql tabel right now, I am using varchar(255) in a column named "bestillinger", but it can only store 255 chars. So I searched a bit, and remembered that i could use longtext or just text, but now when i try to do it, i get a error saying:
我正在尝试制作订单系统，但我现在卡住了。现在在 mysql 表中，我在名为“bestillinger”的列中使用 varchar(255)，但它只能存储 255 个字符。所以我搜索了一下，并记得我可以使用长文本或仅使用文本，但是现在当我尝试这样做时，我收到一条错误消息：
#1170 - BLOB/TEXT column 'bestilling' used in key specification without a key length
I have tried to search here and in Google, but got no luck with me.
我曾尝试在此处和 Google 中进行搜索，但我运气不佳。
My MySQL tabel is:
我的 MySQL 表是：
CREATE TABLE IF NOT EXISTS `bestillinger` (
`id` int(11) NOT NULL,
`bestilling` TEXT NOT NULL PRIMARY KEY,
`accepted` varchar(255) DEFAULT NULL,
UNIQUE KEY `id_bestilling` (`id`,`bestilling`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I am using UNIQUE KEY because I am using "ON DUPLICATE KEY UPDATE" in my PHP part. But don't mind that.
我使用 UNIQUE KEY 因为我在我的 PHP 部分使用“ON DUPLICATE KEY UPDATE”。不过别介意。
Thanks in advance.
提前致谢。
回答by jcho360
you can't set a text as a primary key. Mysql only can Index some characters, and type text could be too big to index.
您不能将文本设置为主键。Mysql 只能索引一些字符，而且类型文本可能太大而无法索引。
take a look here:
看看这里：
回答by Vic
From your definition above, and the verbose monologue in this answer below I suggest the following revision:
根据您上面的定义，以及下面这个答案中的冗长独白，我建议进行以下修改：
CREATE TABLE IF NOT EXISTS `bestillinger` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`bestilling` TEXT NOT NULL,
`hashcode` CHAR(32) AS (MD5(bestilling)),
`accepted` VARCHAR(255) DEFAULT NULL,
UNIQUE KEY `id_bestilling` (hashcode,bestilling(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Specific to MySQL, the default max length for a single-column index key is 1000 byteslong (767 bytes for InnoDB tables, unless you have innodb_large_prefix set). The same length limit applies to any index key prefix (the first N characters for CHAR, VARCHAR, TEXT or first N bytes for BINARY, VARBINARY, BLOB). Note the character versus byte difference; assuming a UTF-8 character set and the maximum of 3 bytes for each character, you might hit this limit with a column prefix index of more than 333 characterson a TEXT or VARCHAR column. In practice, 333 tends to be a good max for me.
特定于 MySQL，单列索引键的默认最大长度为1000 字节（InnoDB 表为 767 字节，除非您设置了 innodb_large_prefix ）。相同的长度限制适用于任何索引键前缀（CHAR、VARCHAR、TEXT 的前 N 个字符或 BINARY、VARBINARY、BLOB 的前 N 个字节）。注意字符与字节的区别；假设使用 UTF-8 字符集并且每个字符最多 3 个字节，您可能会在 TEXT 或 VARCHAR 列上超过333 个字符的列前缀索引达到此限制。在实践中，333 对我来说往往是一个很好的最大值。
Similarly in SQL Server, there is a 900-byte limit for the maximum total size of all index key columns.
同样，在 SQL Server 中，所有索引键列的最大总大小有 900 字节的限制。
But that only uses the first characters of your TEXT as your key, with the obvious collisions imminent.
但这仅使用 TEXT 的第一个字符作为键，明显的冲突迫在眉睫。
In the accepted answer, the suggestion is to use a FULLTEXT index. A FULLTEXT index is specially designed for text searching, and it has not-so-good performance for INSERT/DELETE since it maintains an N-gram over the vocabulary of the column records and stores the resulting vector. This would work if every operation were to use text search functions in a where clause...but not for a unique index. There is also both a primary key and a unique key defined on 'id', which seems redundant or I miss the intent.
在接受的答案中，建议使用FULLTEXT index。FULLTEXT 索引是专为文本搜索而设计的，它在 INSERT/DELETE 方面的性能不太好，因为它在列记录的词汇表上维护一个 N-gram 并存储结果向量。如果每个操作都在 where 子句中使用文本搜索功能，这将起作用……但不适用于唯一索引。在“id”上还定义了一个主键和一个唯一键，这似乎是多余的，或者我错过了意图。
Instead, I suggest a computed hash. you'd be correct to point out there is a chance (Birthday Paradox) that there will be a collision with a hash, so a UNIQUE index alone isn't enough. We'll couple it with a column prefix index, as described above, to give us faith in the uniqueness.
相反，我建议使用计算散列。您指出有可能（生日悖论）与哈希发生冲突是正确的，因此仅使用 UNIQUE 索引是不够的。如上所述，我们将它与列前缀索引结合起来，让我们相信它的唯一性。
I gather the intent of your ID column is to allow proper foreign key referencing from other tables. Quite right, but then that would be the more useful primary key for this table. As well, int(11) refers to the display width of the column, not the underlying value. I put an unsigned auto_increment on 'id' as well, to clarify its role for the larger audience.
我收集您的 ID 列的意图是允许从其他表中正确引用外键。非常正确，但那将是该表更有用的主键。同样， int(11) 指的是列的显示宽度，而不是基础值。我还在 'id' 上放置了一个未签名的 auto_increment，以阐明它对更多观众的作用。
And that brings us to the proposed design above.
这让我们想到了上面提出的设计。
回答by Abhishek Goel
use this
用这个
CREATE TABLE IF NOT EXISTS `bestillinger` (
`id` int(11) NOT NULL,
`bestilling` TEXT NOT NULL PRIMARY KEY,
`accepted` varchar(255) DEFAULT NULL,
UNIQUE KEY `id_bestilling` (`id`,`bestilling`(767))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
767is the limit in mysql while dealing with blob/text indexes
767是 mysql 在处理 blob/text 索引时的限制
Ref : http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
参考：http: //dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
回答by hygull
I think the following example will best explain this problem.
我认为下面的例子最能解释这个问题。
I got the problem because I was trying to set a text field to UNIQUE. I fixed the problem by changing data type of email(TEXT) to email(VARCHAR(254)).
我遇到了问题，因为我试图将文本字段设置为 UNIQUE。我通过将电子邮件（文本）的数据类型更改为电子邮件（VARCHAR（254））来解决该问题。
mysql> desc users;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| fname | varchar(50) | NO | | NULL | |
| lname | varchar(50) | NO | | NULL | |
| uname | varchar(20) | NO | | NULL | |
| email | text | NO | | NULL | |
| contact | bigint(12) | NO | | NULL | |
| profile_pic | text | NO | | NULL | |
| password | varchar(20) | NO | | admin | |
+-------------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> ALTER TABLE users ADD UNIQUE(email);
ERROR 1170 (42000): BLOB/TEXT column 'email' used in key specification without a key length
mysql>
mysql> ALTER TABLE users MODIFY email VARCHAR(254);
Query OK, 9 rows affected (0.02 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE users ADD UNIQUE(email);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| fname | varchar(50) | NO | | NULL | |
| lname | varchar(50) | NO | | NULL | |
| uname | varchar(20) | NO | | NULL | |
| email | varchar(254) | YES | UNI | NULL | |
| contact | bigint(12) | NO | | NULL | |
| profile_pic | text | NO | | NULL | |
| password | varchar(20) | NO | | admin | |
+-------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql>
回答by S.M. Turag
enter image description hereTry to use all columnlength in varchar. Text should be not allowed because of size.
在此处输入图像描述尝试使用varchar 中的所有列长度。由于大小，不应允许使用文本。
