What is wrong with this statement?

Question

mysql> create table newsgroup( -> id integer unsigned NOT NULL AUTO_INCREMENT, -> creater integer unsigned NOT NULL, -> coremember integer unsigned DEFAULT NULL, -> name varchar(300) not null unique, -> description text, -> created datetime not null, -> PRIMARY KEY (id) -> ); ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes mysql> 

I changed 300 to 250 , and that's fine. But I really don't get it.

Answer 1

Database encoding set to UTF8

The UTF8 character can take up to 3 bytes in MySQL , so 767 bytes is 255 .

Creating a UNIQUE index for such long text fields is not recommended.

Create a simple prefix index instead

 CREATE INDEX ix_newsgroup_name ON newsgroup (name (30)) 

which is enough for prefix searches and add another column to store the MD5 hash, which will ensure uniqueness.

Answer 2

767 bytes is a prefix restriction for InnoDB tables. :)

See here: http://dev.mysql.com/doc/refman/5.1/en/create-index.html

Answer 3

You are using utf-8 or even heavier encoding, and therefore each character is represented by one, two, three or four bytes. In MySQL, utf8 stands for the maximum 3-byte sequence, and utf8mb4 for the maximum 4-byte sequence.

Answer 4

remove UNIQUE from varchar.