MySQL: how to change varchar (255) UNIQUE column to UNIQUE Text NOT NULL?

Question

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

NOTE. A column designed to change its property type is a combination of the UNIQUE KEY of another column. For instance.

UNIQUE KEY (name, description)

The column description is currently in varchar (255). Because of this, it cannot be changed:

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

I need it to be TEXT, otherwise I need to recreate all this? I already got some tedious and important data. It will be problematic to recreate.

Answer 1

Are you going to use the TEXT column as part of the UNIQUE KEY? This is VERY inefficient! Do not do this! I highly recommend you:

• Add an additional column named, for example, 'description_hash' char(32) not null default ''
• Save the hash value for the description field. E.g. description_hash=MD5(description)
• Change your key to UNIQUE KEY (name, description_hash)

Of course, you will need to keep the description_hash column last in your code, but as you can see, in most cases only a few code changes are required. Or you can use a trigger to handle this.

Answer 2

I had exactly the same problem.

I added a new char (32) column (I named it hash and added a unique index to it) and two triggers.

 delimiter | CREATE TRIGGER insert_set_hash BEFORE INSERT ON my_table_name FOR EACH ROW BEGIN SET NEW.hash = MD5(NEW.my_text); END; | CREATE TRIGGER update_set_hash BEFORE UPDATE ON my_table_name FOR EACH ROW BEGIN SET NEW.hash = MD5(NEW.my_text); END; | delimiter ; 

By using triggers and adding the UNIQUE index to the hash, you can be sure that the hash values ​​will always be relevant and unique.

Answer 3

 alter table your_table modify column your_column text not null; 

for a unique key

 alter table your_table add unique index your_index_name (your_column(your_length)); 

your_length = allow 1000 bytes

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

---

Both lengths (description + other column cannot exceed 1000), therefore

 alter table your_table add unique index your_index_name (description(800), another_column(200));