保持TEXT字段在MySQL数据库中唯一的最佳方法 [英] Best way of keeping TEXT field unique in MySQL database

问题描述

我想在我的MySQL表中使TEXT字段的值唯一。

I want make value of TEXT field unique in my MySQL table.

经过小规模的研究后，我发现由于性能问题，每个人都不鼓励在TEXT字段上使用UNIQUE INDEX。我现在想要使用的是：

After small research I discovered that everybody are discouraging using UNIQUE INDEX on TEXT fields, due to performance issues. What I want to use now is:

1）创建另一个字段以包含TEXT值的哈希值（md5（text_value））

1) create another field to contain hash of TEXT value (md5(text_value))

2）使这个哈希字段UNIQUE索引

2) make this hash field UNIQUE index

3）在查询中使用INSERT IGNORE

3) use INSERT IGNORE in queries

这个解决方案是完整，安全和最佳的吗？ （在SO上找到）

Is this solution complete, secure and optimal? (found it on SO)

是否有更好的方法来实现这一目标？

Is there a better way of achiving this goal?

推荐答案

我在评论中被问到如何解决这个问题，我会把它写成回复。

As I was asked in the comments how I would solve this, I'll write it as a response.

出现这种情况表明错误在应用程序设计中。考虑这意味着什么。

Being in such a situation suggests mistakes in the application design. Consider what that means.

你有一个文本，你不能提前指定长度，哪个可以非常长（最多64k），你想要的保持独特性。想象一下，将如此大量的数据拆分为单独的密钥，并组成复合索引以生成唯一性。这就是你要做的。对于整数，这将是16000个整数的索引，以复合索引连接。

You have a text of which you cannot specify the length in advance, and which can be extremely long (up to 64k), of which you want to keep uniqueness. Imagine such an amount of data split into separate keys, and composing a composite index to generate uniqueness. This is what you're trying to do. For integers, this would be an index of 16000 integers, joined in a composite index.

进一步考虑CHARACTER类型字段（CHAR，VARCHAR，TEXT）通过编码进行解释，这进一步使问题复杂化。

Consider further that CHARACTER type fields (CHAR, VARCHAR, TEXT) underly interpretation by encoding, which further complicates the issue.

我强烈建议以某种方式分割数据。这不仅可以使DBMS免于合并可变长度的字符块，而且还可以为部分数据生成复合键。也许您甚至可以为您的数据找到更好的存储解决方案。

I'd highly recommend splitting the data up somehow. This not only frees the DBMS from incorporating variable length character blocks, but also might give some possibility of generating composite keys over parts of the data. Maybe you could even find a better storage solution for your data.

如果您有疑问，我建议发布表和/或数据库结构并解释什么是逻辑数据包含TEXT字段，以及为什么您认为它必须是唯一的。

If you have questions, I'd suggest posting the table and/or database structure and explaining what logical data the TEXT field contains, and why you think it would need to be unique.

这篇关于保持TEXT字段在MySQL数据库中唯一的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！