在mysql表中使用TEXT字段的问题 [英] Problem in using TEXT field in mysql Table

问题描述

这是一个非常直接的问题，但是我还没有找到任何解决方案. 场景是我的数据库中有2个表.第二个表的来源取决于第一个表的SOURCE_URL(可能超过255个字符，因此我使用了TEXT).

This is very straight problem but I haven't figured out any solution yet. Scenario is I have 2 table in my DB. Source of 2nd table is dependent on 1st table's SOURCE_URL(can be more than 255 char so I have used TEXT).

create table SOURCES (
        SOURCES_PK int not null AUTO_INCREMENT primary key,
        SOURCE_URL text not null unique,
        DESCRIPTION varchar(255)
);

create table ASSERTIONGROUP (
        ASSERTION_PK int AUTO_INCREMENT primary key,
        LABEL varchar(255),
        SOURCE text not null,
        foreign key (SOURCE) references SOURCES(SOURCE_URL)
);

我收到此错误-

密钥规范中使用的BLOB/TEXT列"SOURCE"没有密钥长度

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

我在这篇文章中看到了讨论- MySQL错误:密钥规范没有密钥长度.
但找不到任何解决方案.
我可以从第一张表中删除唯一键，但是我无法分配外键约束.

I have seen discussion in this post - MySQL error: key specification without a key length.
But can't figure out any solution.
I can remove unique from 1st table but then I can't assign foreign key constraint.

我知道TEXT字段不能唯一，因此需要寻找替代方案.

I know that TEXT field can't be unique so looking for an alternative.

推荐答案

要与较长的varchar或blob列进行匹配，您需要指定索引长度:

In order to match against a long varchar or blob column you'll need to specify the index length:

create table SOURCES (
  SOURCES_PK int not null AUTO_INCREMENT primary key,                    
  SOURCE_URL text not null unique,                    
  DESCRIPTION varchar(255),
  INDEX source_url (source_url(100)) );
//  Key length ----------------^^^ 

对于MyISAM

最大密钥长度为1000个字节.也可以通过更改源代码并重新编译来更改.对于密钥长于250字节的情况，将使用比默认值1024字节更大的密钥块大小.

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

对于InnODB

索引键前缀最多可以为767个字节.参见第12.1.8节创建索引语法" .

请参阅: http://dev.mysql.com/doc/refman/5.5/en/create-table.html

这篇关于在mysql表中使用TEXT字段的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！