問(wèn)題描述

創(chuàng)建 MySQL 表時(shí) PRIMARY、UNIQUE、INDEX 和 FULLTEXT 有什么區(qū)別?

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

我將如何使用它們?

推薦答案

差異

• KEY 或 INDEX 是指正常的非唯一索引.允許索引的非不同值，因此索引可能包含在索引的所有列中具有相同值的行.這些索引不會(huì)對(duì)您的數(shù)據(jù)施加任何限制，因此它們僅用于訪(fǎng)問(wèn) - 無(wú)需掃描所有記錄即可快速到達(dá)特定范圍的記錄.

  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 access - for quickly reaching certain ranges of records without scanning all records.

    UNIQUE 是指索引的所有行都必須唯一的索引.也就是說(shuō)，對(duì)于該索引中的所有列，同一行可能不具有與另一行相同的非 NULL 值.UNIQUE 索引除了用于快速到達(dá)某些記錄范圍外，還可以用于強(qiáng)制限制數(shù)據(jù)，因?yàn)閿?shù)據(jù)庫(kù)系統(tǒng)不允許在插入或更新數(shù)據(jù)時(shí)破壞不同值規(guī)則.

    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 quickly reach certain record ranges, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow the distinct values rule to be broken when inserting or updating data.

    您的數(shù)據(jù)庫(kù)系統(tǒng)可能允許將 UNIQUE 索引應(yīng)用于允許 NULL 值的列，在這種情況下，如果兩行都包含 NULL 值，則允許它們相同(這里的基本原理是認(rèn)為 NULL 不等于本身).但是，根據(jù)您的應(yīng)用程序，您可能發(fā)現(xiàn)這是不受歡迎的:如果您希望防止這種情況發(fā)生，您應(yīng)該禁止相關(guān)列中的 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"，并且一張表上可能只有一個(gè)(并且應(yīng)該 始終為一個(gè)；盡管某些數(shù)據(jù)庫(kù)系統(tǒng)不強(qiáng)制執(zhí)行此操作).PRIMARY 索引旨在作為唯一標(biāo)識(shí)表中任何行的主要方法，因此與 UNIQUE 不同，它不應(yīng)用于任何允許 NULL 值的列.您的 PRIMARY 索引應(yīng)該位于足以唯一標(biāo)識(shí)行的最少列數(shù)上.通常，這只是包含唯一自動(dòng)遞增數(shù)字的一列，但如果還有其他任何東西可以唯一標(biāo)識(shí)一行，例如國(guó)家/地區(qū)代碼"在國(guó)家/地區(qū)列表中，您可以改用它.

    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.

    某些數(shù)據(jù)庫(kù)系統(tǒng)(例如 MySQL 的 InnoDB)會(huì)按照它們?cè)?PRIMARY 索引中出現(xiàn)的順序?qū)⒈淼挠涗洿鎯?chǔ)在磁盤(pán)上.

    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 索引與上述所有索引都不同，它們的行為在數(shù)據(jù)庫(kù)系統(tǒng)之間存在顯著差異.FULLTEXT 索引僅對(duì)使用 MATCH()/AGAINST() 子句完成的全文搜索有用，與上述三個(gè)不同 - 通常使用 b 樹(shù)在內(nèi)部實(shí)現(xiàn)(允許從最左邊的列開(kāi)始選擇、排序或范圍)或哈希表(允許從最左邊的列開(kāi)始選擇).

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

    在其他索引類(lèi)型是通用的情況下，F(xiàn)ULLTEXT 索引是專(zhuān)門(mén)的，因?yàn)樗?wù)于一個(gè)狹窄的目的:它僅用于全文搜索"；功能.

    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，列順序很重要:要使索引在查詢(xún)中有用，查詢(xún)必須使用從左側(cè)開(kāi)始的索引中的列 - 它不能只使用第二個(gè)，索引的第三或第四部分，除非它還使用索引中的前一列來(lái)匹配靜態(tài)值.(要使 FULLTEXT 索引對(duì)查詢(xún)有用，查詢(xún)必須使用索引的所有列.)

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

    這篇關(guān)于MySQL 中 INDEX、PRIMARY、UNIQUE、FULLTEXT 之間的區(qū)別?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

    【網(wǎng)站聲明】本站部分內(nèi)容來(lái)源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問(wèn)題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！