mysql text unique,MySQL：如何将varchar（255）UNIQUE列更改为UNIQUE Text NOT NULL？

The current column is a VARCHAR(255) NOT NULL, so how do I change it to TEXT NOT NULL?

NOTE: The column intended to be changed its property type is a UNIQUE KEY combination of another column. E.g.

UNIQUE KEY (Name, Description)

The column description is currently in varchar(255). It can't be changed because of this:

ERROR 1170 (42000): BLOB/TEXT column 'description' used in key specification without a key length

I need it to be TEXT, else I need to recreate the whole thing?? I got some tedious and important data already in it. It's going to be troublesome to recreate.

解决方案

Are you going to use TEXT column as part of UNIQUE KEY?

It's VERY inefficient! Don't do that!

I'm strongly suggest you to:

Add additional column named for example 'description_hash' char(32) not null default ''

Store the hash-value for description field into it. For ex. description_hash=MD5(description)

Change your key to UNIQUE KEY (name, description_hash)

Ofcourse you'll need to keep the description_hash column up-to-date in your code, but as see - in most cases it is require just few code changes.

Or you can use trigger for handling this.