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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
user198729
  • 61,774
  • 108
  • 250
  • 348
  • What is the MySQL server version? – vfn Jan 26 '10 at 13:40
  • Relative answers are [this](http://stackoverflow.com/questions/6157823/specified-key-was-too-long-max-key-length-is-1000-bytes/6158040#6158040) and [that](http://stackoverflow.com/questions/4131206/mysql-key-was-too-long-issue/4139541#4139541). – dma_k Nov 10 '11 at 10:37

4 Answers4

6

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
1

767 bytes is the stated prefix limititation for InnoDB tables. :)

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

dmnkhhn
  • 1,013
  • 1
  • 10
  • 18
0

You're using utf-8 or even a heavier charset and thus every symbol is represented by one, two, three or four bytes. In MySQL utf8 stands for max 3-byte sequence, and utf8mb4 for max 4-byte sequence.

dma_k
  • 10,431
  • 16
  • 76
  • 128
raveren
  • 17,799
  • 12
  • 70
  • 83
-1

remove UNIQUE from the varchar.

Alex Brown
  • 41,819
  • 10
  • 94
  • 108