Problem in using TEXT field in mysql Table

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) );

I am getting this error-

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

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.

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

Answer1:

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 ----------------^^^

<strong>For MyISAM</strong>

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.

<strong>For InnODB</strong>

Index key prefixes can be up to 767 bytes. See Section 12.1.8, “CREATE INDEX Syntax”.

See: http://dev.mysql.com/doc/refman/5.5/en/create-table.html

Answer2:

Unfortunatly you can not index a whole BLOB and TEXT because index key are limited in length.

You can create FullText index on myasam engine only.

When I need to implement a uniqueness constraint I usually use a specific columns which contains a Hash (SHA or MD5) of the Text, and some code to handle Hash clash.

It is a little bit ugly but it works

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Problem in using TEXT field in mysql Table