mysql - 错误: BLOB/TEXT column 'multimedia' used in key specification without a key length

Question

我尝试创建一个表来存储图像、视频或音频，但收到此错误消息:

“错误: key 规范中使用的 BLOB/TEXT 列“多媒体”没有 key 长度”

Create Table Multimedia
(
uname varchar(25),
datetime timestamp,
multimedia blob,
primary key (uname,datetime,multimedia),
foreign key (uname) references Entry(uname),
foreign key (datetime) references Entry(datetime)
);

有谁知道怎么解决吗？

Answer 1

根据 Prefixing INDEXes 上的文档:

When you index a BLOB or TEXT column, you must specify a prefix length for the index.

For example:

CREATE TABLE test ( blob_col BLOB, INDEX( blob_col( 10 ) ) );

Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables, unless you have innodb_large_prefix set).

更改表主键定义如下:

Create Table Multimedia
(
  uname varchar(25),
  datetime timestamp,
  multimedia blob,

  primary key ( uname, datetime, multimedia( 767 ) ), -- <--- changed

  foreign key ( uname ) references Entry( uname ),
  foreign key ( datetime ) references Entry( datetime )
);

您还可以考虑更改设计，将多媒体对象存储在不同的表中，并在其上带有唯一键和自动增量主键编号。您可以在此表中使用相同的 multimedia_id 来形成主键，如下所示:

  primary key ( uname, datetime, multimedia_id ),
  foreign key ( uname ) references Entry( uname ),
  foreign key ( datetime ) references Entry( datetime ),
  foreign key ( multimedia_id ) references multimedia_objects( id )