MySQL supports indexes on generated columns. MySQL支持生成列的索引。For example:例如：
CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));
The generated column, 生成的列gc, is defined as the expression f1 + 1. gc被定义为表达式f1+1。The column is also indexed and the optimizer can take that index into account during execution plan construction. 列也被索引，优化器可以在执行计划构建期间考虑该索引。In the following query, the 在以下查询中，WHERE clause refers to gc and the optimizer considers whether the index on that column yields a more efficient plan:WHERE子句引用gc，优化器考虑该列上的索引是否生成更高效的计划：
SELECT * FROM t1 WHERE gc > 9;
The optimizer can use indexes on generated columns to generate execution plans, even in the absence of direct references in queries to those columns by name. 优化器可以在生成的列上使用索引来生成执行计划，即使在按名称查询这些列时没有直接引用。This occurs if the 如果WHERE, ORDER BY, or GROUP BY clause refers to an expression that matches the definition of some indexed generated column. WHERE、ORDER BY或GROUP BY子句引用的表达式与某个索引生成列的定义相匹配，则会发生这种情况。The following query does not refer directly to 以下查询不直接引用gc but does use an expression that matches the definition of gc:gc，而是使用与gc定义匹配的表达式：
SELECT * FROM t1 WHERE f1 + 1 > 9;
The optimizer recognizes that the expression 优化器识别出表达式f1 + 1 matches the definition of gc and that gc is indexed, so it considers that index during execution plan construction. f1 + 1与gc的定义相匹配，并且gc被索引，因此它在执行计划构建期间考虑该索引。You can see this using 您可以使用EXPLAIN:EXPLAIN来查看这一点：
mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: gc
key: gc
key_len: 5
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
In effect, the optimizer has replaced the expression 实际上，优化器已将表达式f1 + 1 with the name of the generated column that matches the expression. f1 + 1替换为与表达式匹配的生成列的名称。That is also apparent in the rewritten query available in the extended 这一点在EXPLAIN information displayed by SHOW WARNINGS:SHOW WARNINGS显示的扩展EXPLAIN信息中的重写查询中也很明显：
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)
The following restrictions and conditions apply to the optimizer's use of generated column indexes:以下限制和条件适用于优化器使用生成的列索引：
For a query expression to match a generated column definition, the expression must be identical and it must have the same result type. 要使查询表达式与生成的列定义匹配，该表达式必须相同，并且必须具有相同的结果类型。For example, if the generated column expression is 例如，如果生成的列表达式为f1 + 1, the optimizer does not recognize a match if the query uses 1 + f1, or if f1 + 1 (an integer expression) is compared with a string.f1+1，则如果查询使用1+f1，或者如果将f1+1（整数表达式）与字符串进行比较，则优化器将无法识别匹配项。
The optimization applies to these operators: 优化应用于这些运算符：=, <, <=, >, >=, BETWEEN, and IN().=、<、<=、>、>=、BETWEEN和IN()。
For operators other than 对于除BETWEEN and IN(), either operand can be replaced by a matching generated column. BETWEEN和IN()之外的运算符，任何一个操作数都可以替换为匹配的生成列。For 对于BETWEEN and IN(), only the first argument can be replaced by a matching generated column, and the other arguments must have the same result type. BETWEEN和IN()，只有第一个参数可以替换为匹配的生成列，其他参数必须具有相同的结果类型。BETWEEN and IN() are not yet supported for comparisons involving JSON values.BETWEEN和IN()。
The generated column must be defined as an expression that contains at least a function call or one of the operators mentioned in the preceding item. 生成的列必须定义为至少包含函数调用或上一项中提到的一个运算符的表达式。The expression cannot consist of a simple reference to another column. 表达式不能包含对另一列的简单引用。For example, 例如，gc INT AS (f1) STORED consists only of a column reference, so indexes on gc are not considered.gc INT AS (f1) STORED只包含一个列引用，因此不考虑gc上的索引。
For comparisons of strings to indexed generated columns that compute a value from a JSON function that returns a quoted string, 要将字符串与从返回带引号字符串的JSON函数计算值的索引生成列进行比较，需要在列定义中使用JSON_UNQUOTE() is needed in the column definition to remove the extra quotes from the function value. JSON_UNQUOTE()从函数值中删除额外的引号。(For direct comparison of a string to the function result, the JSON comparator handles quote removal, but this does not occur for index lookups.) （对于字符串与函数结果的直接比较，JSON comparator处理引号删除，但对于索引查找不会发生这种情况。）For example, instead of writing a column definition like this:例如，不要像这样编写列定义：
doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED
Write it like this:这样写：
doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED
With the latter definition, the optimizer can detect a match for both of these comparisons:使用后一种定义，优化器可以检测这两种比较的匹配：
... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ... ... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...
Without 如果列定义中没有JSON_UNQUOTE() in the column definition, the optimizer detects a match only for the first of those comparisons.JSON_UNQUOTE()，优化器将仅检测第一个比较的匹配项。
If the optimizer picks the wrong index, an index hint can be used to disable it and force the optimizer to make a different choice.如果优化器选择了错误的索引，可以使用索引提示来禁用它，并强制优化器做出不同的选择。