MySQL：“唯一”列字段是否意味着索引，如果是，为什么？ [英] MySQL: Does a 'unique' column field imply an index, and if so, why?

问题描述

虽然我理解，在一个唯一的列上有一个索引是非常方便的 - 容易和有效地检查值冲突，我认为应该由程序员指定一个唯一字段列是否有一个索引

Although I understand that it is very convinient to have an index on a unique column - to easily and efficiently check for value collision, I think it should be up to the programmer to specify whether a unique field column is to have an index or not, with regards to size- vs speed- optimization.

据我所知，MySQL自动索引指定为唯一的表列。是这样吗？

As far as I understand MySQL automatically indexes a table column that is specified as unique. Is it so?

有没有什么理由MySQL似乎不能有唯一列WITHOUT索引？是的，每个值插入/更新都将是O（number_of_rows）操作没有一个，但是像我说，程序员不应该纳税的决定？

Is there any reason MySQL seemingly cannot have unique columns WITHOUT indexes? Yes, every value insertion/update will be O(number_of_rows) operation without one, but like I said, shouldn't the programmer be taxed with the decision?

只是好奇！

推荐答案

据我所知，MySQL自动索引指定为唯一的表列。是这样吗？

As far as I understand MySQL automatically indexes a table column that is specified as unique. Is it so?

是的。它不一定是自动的，虽然，它是隐含的。 UNIQUE 是索引的类型，因此当然会对该列进行索引。

Yes. It's not necessarily 'automatic' though, it's implicit. UNIQUE is type of index, so of course that column is indexed.

有没有什么理由MySQL似乎不能有唯一的列WITHOUT索引？

Is there any reason MySQL seemingly cannot have unique columns WITHOUT indexes?

因为MySQL对每个 INSERT / UPDATE 执行全表扫描以维护唯一约束。

Because it would be silly for MySQL to do a full table scan on each INSERT/UPDATE to uphold the unique constraint.

修改：

是，每次插入/更新值都将为O（number_of_rows ）操作没有一个，但是像我说的，应该不应该程序员纳税的决定？

Yes, every value insertion/update will be O(number_of_rows) operation without one, but like I said, shouldn't the programmer be taxed with the decision?

为什么程序员想要手动强制数据存储层之外的基本数据完整性？我明白你要这样做，但事实是一个索引不可能伤害任何东西（它只是需要一点点空间） - 所以真的没有一个决定要考虑。

Why would a programmer want to manually enforce basic data integrity outside of the data storage layer? I understand where you're going with this, but the fact is an index can't possibly hurt anything (it just takes a bit more space) - so there really isn't a decision to consider.

这篇关于MySQL：“唯一”列字段是否意味着索引，如果是，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！