是否可以在 MySQL 中使用基于函数的索引?
我记得在 Oracle 中可以基于函数进行索引，例如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(第一次编写此答案时的最新版本)具有此功能.值得注意的是，8.0.13 及以上版本现在支持 功能索引，让您无需使用下面描述的触发方法即可实现所需.
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.
如果您运行的是旧版本的 mysql，则 可能只使用列的 领先 部分(此功能具有已经存在很长时间了)，但不是从第二个或后续字符开始，或任何其他更复杂的函数.
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.
例如，以下内容使用名称的前五个字符创建索引:
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.
而且，虽然它在技术上违反了 3NF，但可以通过使用触发器来保持数据同步来缓解这种情况(通常这样做是为了提高性能).
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).
相关文章