是否可以在 MySQL 中使用基于函数的索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10595037/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to have function-based index in MySQL?
提问by user836026
I recall in Oracle it is possible to index based on a function, e.g. SUBSTRING(id,1,8).
我记得在 Oracle 中可以基于函数进行索引，例如SUBSTRING(id,1,8).
Does MySQL support this? If not, is there is any alternative?
MySQL 支持吗？如果没有，是否有其他选择？
采纳答案by paxdiablo
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.
不，不是一般意义上的，我什至不相信 5.6（首次编写此答案时的最新版本）具有此功能。值得注意的是，8.0.13 及以上版本现在支持函数式索引，让您无需下面介绍的触发器方法即可实现所需。
See https://dev.mysql.com/doc/refman/8.0/en/create-index.htmlfor more detail on this.
有关更多详细信息，请参阅https://dev.mysql.com/doc/refman/8.0/en/create-index.html。
If you are running an older version of mysql, it ispossible to only use the leadingpart 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.
如果您运行的是较旧版本的mysql，则可以仅使用列的前导部分（此功能已存在很长时间），而不是从第二个或后续字符开始的部分，或任何其他更复杂的功能.
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 anothercolumn 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).
而且，虽然它在技术上违反了 3NF，但通过使用触发器来保持数据同步可以减轻这种情况（这通常是为了增加性能而做的）。
回答by axxis
MySQL supports this since 8.0.13
MySQL 从8.0.13 开始支持这个
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts
MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table. Examples:
MySQL 8.0.13 及更高版本支持索引表达式值而不是列或列前缀值的功能键部分。使用功能关键部分可以对未直接存储在表中的值进行索引。例子：
CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
An index with multiple key parts can mix nonfunctional and functional key parts.
具有多个关键部分的索引可以混合非功能性和功能性关键部分。
For versions before 8.0.13 there are the following alternatives:
对于 8.0.13 之前的版本，有以下替代方案：
1. Since MySQL 5.7.6
1. 从 MySQL 5.7.6 开始
You can use an auto generated column to hold the substring with an index on it:
您可以使用自动生成的列来保存带有索引的子字符串：
CREATE TABLE SomeTable (
id CHAR(10),
sub_id CHAR(8) AS SUBSTRING(id, 1, 8) STORED, INDEX(sub_id)
)
As Benjaminnoted, InnoDB supports secondary indexes on virtual columns so the STORED keyword can be ommitted. In fact secondary indexes on virtual columns may be preferable. More info here: Secondary Indexes and Generated Columns
正如Benjamin 所指出的，InnoDB 支持虚拟列上的二级索引，因此可以省略 STORED 关键字。事实上，虚拟列上的二级索引可能更可取。更多信息：二级索引和生成的列
2. Before MySQL 5.7.6
2. MySQL 5.7.6 之前
You can use a column updated by a trigger with an index on it:
您可以使用由带有索引的触发器更新的列：
CREATE TABLE SomeTable (
id CHAR(10),
sub_id CHAR(8) , INDEX(sub_id)
);
CREATE TRIGGER TR_SomeTable_INSERT_sub_id
BEFORE INSERT
ON SomeTable FOR EACH ROW
SET NEW.sub_id = SUBSTRING(NEW.id, 1, 8);
CREATE TRIGGER TR_SomeTable_UPDATE_sub_id
BEFORE UPDATE
ON SomeTable FOR EACH ROW
SET NEW.sub_id = SUBSTRING(NEW.id, 1, 8);
回答by Gabe Martin-Dempesy
This is possible as of MySQL 5.7.5 using the new Generated Columns.
从 MySQL 5.7.5 开始，这可以使用新的Generated Columns 实现。
回答by aakash muthuramalingam
Yes, we can create functional index in MySQL. This feature is available from MySQL 8.0.13. (Other RDBMS have this feature in its earlier releases But MySQL introduced it in its release 8.0.13). Functional Index
是的，我们可以在 MySQL 中创建功能索引。此功能从 MySQL 8.0.13 开始可用。（其他 RDBMS 在其早期版本中具有此功能，但 MySQL 在其版本 8.0.13 中引入了它）。功能指标
Here I have given an example for creating functional index in MySQL8.0
这里我给出了一个在 MySQL8.0 中创建函数式索引的例子
This is the query
这是查询
SELECT * FROM app_user WHERE month(createdOn) = 5;
mysql> SELECT * FROM app_user WHERE month(createdOn) = 5;
7e9e2b7bc2e9bde15504f6c5658458ab - 74322 rows in set (5.01 sec)
It is executing for more than 5 sec to fetch 74322 records even I am having index on createdOn column.(Indexing on createdOn column is not used by optimiser as it is masked by month() function)
即使我在 createdOn 列上有索引，它也会执行超过 5 秒来获取 74322 条记录。（优化器不使用对 createdOn 列的索引，因为它被月（）函数屏蔽了）
Now I have created a functional index on the column using the below syntax.
现在我使用以下语法在列上创建了一个功能索引。
mysql> ALTER TABLE app_user ADD INDEX
idx_month_createdon((month(createdOn)));
Query OK, 0 rows affected (1 min 17.37 sec) Records: 0 Duplicates: 0
Warnings: 0
mysql> SELECT * FROM app_user WHERE month(createdOn) = 5;
7e9e2b7bc2e9bde15504f6c5658458ab - 74322 rows in set (0.29 sec)
After creating the functional index, it is executing in 0.29 secs.
创建功能索引后，它在 0.29 秒内执行。
It is difficult to find functional index document in Mysql website, it is in the name of functional key parts
Mysql网站很难找到功能索引文档，都是以功能关键部分的名字命名的
And We cannot drop the column which is having functional index. To achieve, we need to drop the functional index in the first place.
并且我们不能删除具有功能索引的列。为此，我们首先需要删除功能索引。
mysql> ALTER TABLE app_user DROP COLUMN createdOn;
Cannot drop column 'createdOn' because it is used by a functional
index. In order to drop the column, you must remove the functional
index.
If you are using MySQL > 5.7.5, you can achieve the same using generated columns.
如果您使用 MySQL > 5.7.5，您可以使用生成的列实现相同的效果。
