MySQL中的INDEX，PRIMARY，UNIQUE，FULLTEXT之间的区别？ [英] Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

问题描述

创建MySQL表时PRIMARY，UNIQUE，INDEX和FULLTEXT之间有什么区别？

What are the differences between PRIMARY, UNIQUE, INDEX and FULLTEXT when creating MySQL tables?

我将如何使用它们？

推荐答案

差异

• KEY 或 INDEX 是指正常的非唯一索引。允许索引的非不同值，因此索引可能包含索引的所有列中具有相同值的行。这些索引不会对您的数据施加任何限制，因此它们仅用于确保某些查询可以快速运行。

  Differences

  • KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any restraints on your data so they are used only for making sure certain queries can run quickly.

    UNIQUE 指索引的所有行必须唯一的索引。也就是说，对于此索引中的所有列，同一行可能不具有与另一行相同的非NULL值。除了用于加速查询之外，UNIQUE索引还可用于强制限制数据，因为数据库系统在插入或更新数据时不允许破坏此不同值规则。

    UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.

    您的数据库系统可能允许将UNIQUE索引应用于允许NULL值的列，在这种情况下，如果两行都包含NULL值，则允许两行相同（这里的基本原理是认为NULL不等于自己）。但是，根据您的应用程序，可能发现这种情况：如果您希望阻止这种情况，您应该在相关列中禁止使用NULL值。

    Your database system may allow a UNIQUE index to be applied to columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (the rationale here is that NULL is considered not equal to itself). Depending on your application, however, you may find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns.

    PRIMARY 的行为与UNIQUE索引完全相同，只是它总是命名为'PRIMARY'，并且表中可能只有一个（并且应该永远是一个;虽然有些数据库系统不强制执行此操作）。 PRIMARY索引旨在作为唯一标识表中任何行的主要方法，因此与UNIQUE不同，它不应在任何允许NULL值的列上使用。您的PRIMARY索引应该位于足以唯一标识行的最小列数上。通常，这只是一个包含唯一自动递增数字的列，但如果还有其他任何可以唯一标识行的列，例如国家/地区列表中的国家/地区代码，则可以使用该列。

    PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a primary means to uniquely identify any row in the table, so unlike UNIQUE it should not be used on any columns which allow NULL values. Your PRIMARY index should be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.

    某些数据库系统（例如MySQL的InnoDB）会按照它们在PRIMARY索引中出现的顺序将表记录存储在磁盘上。

    Some database systems (such as MySQL's InnoDB) will store a table's records on disk in the order in which they appear in the PRIMARY index.

    FULLTEXT 索引与上述所有索引不同，它们的行为在数据库系统之间存在显着差异。 FULLTEXT索引仅对使用MATCH（）/ AGAINST（）子句进行的全文搜索很有用，与上述三种不同 - 通常使用b-tree在内部实现（允许从最左列开始选择，排序或范围）或哈希表（允许从最左边的列开始选择）。

    FULLTEXT indexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three - which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).

    如果其他索引类型是通用的，则FULLTEXT索引是专用的，因为它服务于一个狭窄的目的：它仅用于全文搜索功能。

    Where the other index types are general-purpose, a FULLTEXT index is specialised, in that it serves a narrow purpose: it's only used for a "full text search" feature.

    
    

    • 所有这些索引中都可能包含多个列。

    • All of these indexes may have more than one column in them.

    除了FULLTEXT之外，列顺序很重要：要使索引在查询中有用，查询必须使用从左侧开始的索引中的列 - 它不能仅使用索引的第二，第三或第四部分，除非它还使用索引中的前一列来匹配静态值。 （要使FULLTEXT索引对查询有用，查询必须使用索引的所有列。）

    With the exception of FULLTEXT, the column order is significant: for the index to be useful in a query, the query must use columns from the index starting from the left - it can't use just the second, third or fourth part of an index, unless it is also using the previous columns in the index to match static values. (For a FULLTEXT index to be useful to a query, the query must use all columns of the index.)

    这篇关于MySQL中的INDEX，PRIMARY，UNIQUE，FULLTEXT之间的区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！