Search code examples
mysqlmysql-error-1071

What's wrong with this statement?

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 it's ok.But I don't really understand.

Solution

  • Your database encoding is set to UTF8

    An UTF8 character may take up to 3 bytes in MySQL, so 767 bytes is 255 characters.

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

    Instead, create a plain prefixed index

    CREATE INDEX ix_newsgroup_name ON newsgroup (name (30))

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