BLOB/TEXT column 'bestilling' used in key specification without a key length

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)).

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>

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;

767 is the limit in mysql while dealing with blob/text indexes

Ref : http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html


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 bytes long (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 characters on a TEXT or VARCHAR column. In practice, 333 tends to be a good max for me.

Similarly in SQL Server, there is a 900-byte limit for the maximum total size of all index key columns.

But that only uses the first characters of your TEXT as your key, with the obvious collisions imminent.

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.

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.

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.

And that brings us to the proposed design above.


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.

take a look here:

  • http://www.mydigitallife.info/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/

  • MySQL error: key specification without a key length

Tags:

Mysql

Text

Key

Longtext

Recent Posts