問(wèn)題描述

我記得在 Oracle 中可以基于函數(shù)進(jìn)行索引，例如SUBSTRING(id,1,8).

I recall in Oracle it is possible to index based on a function, e.g. SUBSTRING(id,1,8).

MySQL 支持嗎?如果沒有，有沒有其他選擇?

Does MySQL support this? If not, is there is any alternative?

推薦答案

不，不是一般意義上的，我不相信 5.6(第一次編寫此答案時(shí)的最新版本)具有此功能.值得注意的是，8.0.13 及以上版本現(xiàn)在支持 功能索引，讓您無(wú)需使用下面描述的觸發(fā)方法即可實(shí)現(xiàn)所需.

No, not in a general sense, I don't believe even 5.6 (the latest version when this answer was first written) has this functionality. It's worth noting that 8.0.13 and above now support functional indexes, allowing you to achieve what you need without the trigger method described below.

如果您運(yùn)行的是舊版本的 mysql，則 可能只使用列的 領(lǐng)先 部分(此功能具有已經(jīng)存在很長(zhǎng)時(shí)間了)，但不是從第二個(gè)或后續(xù)字符開始，或任何其他更復(fù)雜的函數(shù).

If you are running an older version of mysql, it is possible to only use the leading part of a column (this functionality has been around for a long time), but not one starting at the second or subsequent characters, or any other more complex function.

例如，以下內(nèi)容使用名稱的前五個(gè)字符創(chuàng)建索引:

For example, the following creates an index using the first five characters of a name:

create index name_first_five on cust_table (name(5));

對(duì)于更復(fù)雜的表達(dá)式，您可以通過(guò)在其中包含可索引數(shù)據(jù)的另一個(gè)列來(lái)實(shí)現(xiàn)類似的效果，然后使用插入/更新觸發(fā)器來(lái)確保它被正確填充.

For more complex expressions, you can achieve a similar effect by having another column with the indexable data in it, then using insert/update triggers to ensure it's populated correctly.

除了浪費(fèi)空間用于冗余數(shù)據(jù)之外，這幾乎是一樣的.

Other than the wasted space for redundant data, that's pretty much the same thing.

而且，雖然它在技術(shù)上違反了 3NF，但可以通過(guò)使用觸發(fā)器來(lái)保持?jǐn)?shù)據(jù)同步來(lái)緩解這種情況(通常這樣做是為了提高性能).

And, although it technically violates 3NF, that's mitigated by the use of triggers to keep the data in sync (this is something that's often done for added performance).

這篇關(guān)于是否可以在 MySQL 中使用基于函數(shù)的索引?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！