MySQL:如何将varchar(255)UNIQUE列更改为UNIQUE Text NOT NULL? [英] MySQL: How to alter varchar(255) UNIQUE column to UNIQUE Text NOT NULL?

问题描述

当前列是VARCHAR(255)NOT NULL，那么如何将其更改为TEXT NOT NULL?

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

注意:要更改其属性类型的列是另一列的UNIQUE KEY组合.例如

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

唯一键(名称，说明)

列描述当前位于varchar(255)中.因此无法更改:

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

错误1170(42000):密钥规范中使用的BLOB/TEXT列'description'没有密钥长度

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

我需要它是TEXT，否则我需要重新创建整个东西?我已经有了一些乏味而重要的数据.重新创建会很麻烦.

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.

推荐答案

您是否要将TEXT列用作UNIQUE KEY的一部分? 效率很低！不要那样做！ 我强烈建议您:

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:

• 添加其他列，例如'description_hash' char(32) not null default ''
• 将description字段的哈希值存储到其中.对于前. description_hash=MD5(description)
• 将密钥更改为UNIQUE KEY (name, description_hash)

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

当然，您需要在代码中保持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.

这篇关于MySQL:如何将varchar(255)UNIQUE列更改为UNIQUE Text NOT NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！