是否可以在MySQL中使用基于函数的索引？ [英] Is it possible to have function-based index in MySQL?

问题描述

我记得在Oracle中可以基于一个函数进行索引，例如： SUBSTRING（id，1,8）。

MySQL是否支持此功能？如果没有，是否有其他选择？

解决方案

不，不是一般意义上的，我甚至不相信5.6已经这个功能。 只能使用列的前导部分（此功能已存在很长时间），但不能从第二个或后续字符开始，或者任何其他更复杂的函数。

例如，下面使用名称的前五个字符创建一个索引：

 在cust_table上创建索引name_first_five（name（5））; 
  

对于更复杂的表达式，你可以通过另一个来达到类似的效果其中包含可索引数据的列，然后使用插入/更新触发器确保其正确填充。

除了冗余数据的浪费空间之外，这几乎是一样的。

而且，虽然它在技术上违反了3NF，但通过使用触发器来保持数据同步可以缓解这种情况（这通常是为了增加性能而做的）。 / p>

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

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

解决方案

No, not in a general sense, I don't believe even 5.6 has this functionality. 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.

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

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.

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

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

这篇关于是否可以在MySQL中使用基于函数的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！