CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
index_type:
    USING {BTREE | HASH}
algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

For string columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:对于字符串列，可以创建仅使用列值的前导部分的索引，使用col_name(length)语法指定索引前缀长度：

If a specified index prefix exceeds the maximum column data type size, CREATE INDEX handles the index as follows:如果指定的索引前缀超过最大列数据类型大小，则CREATE INDEX将按如下方式处理索引：

The statement shown here creates an index using the first 10 characters of the name column (assuming that name has a nonbinary string type):此处显示的语句使用name列的前10个字符创建索引（假设name具有非二进制字符串类型）：

CREATE INDEX part_of_name ON customer (name(10));

If names in the column usually differ in the first 10 characters, lookups performed using this index should not be much slower than using an index created from the entire name column. 如果列中的名称在前10个字符中通常不同，则使用此索引执行的查找不应比使用从整个name列创建的索引慢多少。Also, using column prefixes for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.此外，为索引使用列前缀可以使索引文件小得多，这可以节省大量磁盘空间，还可能加快INSERT操作。

A “normal” index indexes column values or prefixes of column values. “普通”索引索引列值或列值的前缀。For example, in the following table, the index entry for a given t1 row includes the full col1 value and a prefix of the col2 value consisting of its first 10 characters:例如，在下表中，给定t1行的索引项包括完整的col1值和col2值的前缀，col2值由其前10个字符组成：

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. MySQL 8.0.13及更高版本支持索引表达式值而不是列或列前缀值的功能关键部分。Use of functional key parts enables indexing of values not stored directly in the table. 使用功能键部件可以对表中未直接存储的值进行索引。Examples:示例：

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.包含多个关键部分的索引可以混合非功能性和功能性关键部分。

ASC and DESC are supported for functional key parts.功能键部分支持ASC和DESC。

Functional key parts must adhere to the following rules. 功能关键部件必须遵守以下规则。An error occurs if a key part definition contains disallowed constructs.如果关键零件定义包含不允许的构件，则会发生错误。

For CREATE TABLE ... LIKE, the destination table preserves functional key parts from the original table.对于CREATE TABLE ... LIKE，目标表保留原始表中的功能键部分。

Functional indexes are implemented as hidden virtual generated columns, which has these implications:函数索引实现为隐藏的虚拟生成列，这具有以下含义：

UNIQUE is supported for indexes that include functional key parts. 包含功能关键部分的索引支持UNIQUE。However, primary keys cannot include functional key parts. 但是，主键不能包括功能键部件。A primary key requires the generated column to be stored, but functional key parts are implemented as virtual generated columns, not stored generated columns.主键要求存储生成的列，但功能键部分实现为虚拟生成列，而不是存储生成列。

SPATIAL and FULLTEXT indexes cannot have functional key parts.SPATIAL（空间）索引和FULLTEXT（全文）索引不能包含功能键部分。

If a table contains no primary key, InnoDB automatically promotes the first UNIQUE NOT NULL index to the primary key. 如果表不包含主键，InnoDB会自动将第一个唯一的非空索引提升到主键。This is not supported for UNIQUE NOT NULL indexes that have functional key parts.对于具有功能键部分的UNIQUE NOT NULL索引，这是不受支持的。

Nonfunctional indexes raise a warning if there are duplicate indexes. 如果存在重复索引，则非功能索引将发出警告。Indexes that contain functional key parts do not have this feature.包含功能键部分的索引没有此功能。

To remove a column that is referenced by a functional key part, the index must be removed first. Otherwise, an error occurs.要删除功能键部件引用的列，必须先删除索引。否则，将发生错误。

Although nonfunctional key parts support a prefix length specification, this is not possible for functional key parts. 尽管非功能性关键部件支持前缀长度规范，但功能性关键部件不支持前缀长度规范。The solution is to use SUBSTRING() (or CAST(), as described later in this section). 解决方案是使用SUBSTRING()（或CAST()，如本节后面所述）。For a functional key part containing the SUBSTRING() function to be used in a query, the WHERE clause must contain SUBSTRING() with the same arguments. 对于包含要在查询中使用的SUBSTRING()函数的函数键部分，WHERE子句必须包含具有相同参数的SUBSTRING()。In the following example, only the second SELECT is able to use the index because that is the only query in which the arguments to SUBSTRING() match the index specification:在以下示例中，只有第二个SELECT可以使用索引，因为这是SUBSTRING()的参数与索引规范匹配的唯一查询：

CREATE TABLE tbl (
  col1 LONGTEXT,
  INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';

Functional key parts enable indexing of values that cannot be indexed otherwise, such as JSON values. 功能键部件支持对无法以其他方式索引的值（如JSON值）进行索引。However, this must be done correctly to achieve the desired effect. 但是，必须正确地执行此操作才能达到预期效果。For example, this syntax does not work:例如，此语法不起作用：

CREATE TABLE employees (
  data JSON,
  INDEX ((data->>'$.name'))
);

The syntax fails because:语法失败，因为：

To index the JSON column, you could try using the CAST() function as follows:要为JSON列编制索引，可以尝试使用CAST()函数，如下所示：

CREATE TABLE employees (
  data JSON,
  INDEX ((CAST(data->>'$.name' AS CHAR(30))))
);

The hidden generated column is assigned the VARCHAR(30) data type, which can be indexed. But this approach produces a new issue when trying to use the index:生成的隐藏列被分配了VARCHAR(30)数据类型，该数据类型可以被索引。但这种方法在尝试使用索引时产生了一个新问题：

As a result, there is a collation mismatch between the indexed expression in the preceding table definition and the WHERE clause expression in the following query:因此，前面表定义中的索引表达式与以下查询中的WHERE子句表达式之间存在排序规则不匹配：

SELECT * FROM employees WHERE data->>'$.name' = 'James';

The index is not used because the expressions in the query and the index differ. 由于查询和索引中的表达式不同，因此未使用索引。To support this kind of scenario for functional key parts, the optimizer automatically strips CAST() when looking for an index to use, but only if the collation of the indexed expression matches that of the query expression. 为了支持函数键部件的这种情况，优化器在查找要使用的索引时自动剥离CAST()，但前提是索引表达式的排序规则与查询表达式的排序规则匹配。For an index with a functional key part to be used, either of the following two solutions work (although they differ somewhat in effect):对于要使用功能键部分的索引，以下两种解决方案中的任何一种都有效（尽管它们在效果上有所不同）：

Be aware that although the optimizer supports automatically stripping CAST() with indexed generated columns, the following approach does not work because it produces a different result with and without an index (Bug#27337092):请注意，尽管优化器支持使用索引生成的列自动剥离CAST()，但以下方法不起作用，因为它在有索引和无索引的情况下生成不同的结果（Bug#27337092）：

mysql> CREATE TABLE employees (
data JSON,
generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
);
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> INSERT INTO employees (data)
VALUES ('{"name": "james"}'), ('{"name": "James"}');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "James"} | James         |
+-------------------+---------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "james"} | james         |
| {"name": "James"} | James         |
+-------------------+---------------+
2 rows in set (0.01 sec)

A UNIQUE index creates a constraint such that all values in the index must be distinct. UNIQUE索引会创建一个约束，使得索引中的所有值都必须是不同的。An error occurs if you try to add a new row with a key value that matches an existing row. 如果尝试添加键值与现有行匹配的新行，则会发生错误。If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. 如果为UNIQUE索引中的列指定前缀值，则列值在前缀长度内必须唯一。A UNIQUE index permits multiple NULL values for columns that can contain NULL.唯一索引允许包含NULL的列具有多个NULL值。

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as follows:如果表的PRIMARY KEY（主键）或UNIQUE NOT NULL索引由一个整型列组成，则可以使用_rowid在SELECT语句中引用索引列，如下所示：

FULLTEXT indexes are supported only for InnoDB and MyISAM tables and can include only CHAR, VARCHAR, and TEXT columns. 只有InnoDB和MyISAM表支持FULLTEXT索引，并且只能包含CHAR列、VARCHAR列和TEXT列。Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. 索引总是发生在整个列上；不支持列前缀索引，如果指定，则忽略任何前缀长度。See Section 12.10, “Full-Text Search Functions”, for details of operation.有关操作的详细信息，请参阅第12.10节，“全文搜索函数”。

As of MySQL 8.0.17, InnoDB supports multi-valued indexes. 从MySQL 8.0.17开始，InnoDB支持多值索引。A multi-valued index is a secondary index defined on a column that stores an array of values. 多值索引是在存储值数组的列上定义的二级索引。A “normal” index has one index record for each data record (1:1). “正常”索引对每个数据记录有一个索引记录（1:1）。A multi-valued index can have multiple index records for a single data record (N:1). 对于单个数据记录（N:1），多值索引可以有多个索引记录。Multi-valued indexes are intended for indexing JSON arrays. 多值索引用于索引JSON数组。For example, a multi-valued index defined on the array of zip codes in the following JSON document creates an index record for each zip code, with each index record referencing the same data record.例如，在下面的JSON文档中，在邮政编码数组上定义的多值索引为每个邮政编码创建一个索引记录，每个索引记录引用相同的数据记录。

{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}

Creating multi-valued Indexes创建多值索引

You can create a multi-valued index in a CREATE TABLE, ALTER TABLE, or CREATE INDEX statement. 您可以在CREATE TABLE、ALTER TABLE或CREATE INDEX语句中创建多值索引。This requires using CAST(... AS ... ARRAY) in the index definition, which casts same-typed scalar values in a JSON array to an SQL data type array. 这需要在索引定义中使用CAST(... AS ... ARRAY)，它将JSON数组中相同类型的标量值强制转换为SQL数据类型数组。A virtual column is then generated transparently with the values in the SQL data type array; finally, a functional index (also referred to as a virtual index) is created on the virtual column. 然后使用SQL数据类型数组中的值透明地生成一个虚拟列；最后，在虚拟列上创建一个函数索引（也称为虚拟索引）。It is the functional index defined on the virtual column of values from the SQL data type array that forms the multi-valued index.它是在构成多值索引的SQL数据类型数组的虚拟值列上定义的函数索引。

The examples in the following list show the three different ways in which a multi-valued index zips can be created on an array $.zipcode on a JSON column custinfo in a table named customers. 下表中的示例显示了三种不同的方法，可以在名为customers的表中JSON列custinfo上的数组$.zipcode上创建多值索引zips。In each case, the JSON array is cast to an SQL data type array of UNSIGNED integer values.在每种情况下，JSON数组都转换为无符号整数值的SQL数据类型数组。

A multi-valued index can also be defined as part of a composite index. 多值索引也可以定义为复合索引的一部分。This example shows a composite index that includes two single-valued parts (for the id and modified columns), and one multi-valued part (for the custinfo column):此示例显示了一个复合索引，其中包括两个单值部分（用于id列和modified列）和一个多值部分（用于custinfo列）：

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

ALTER TABLE customers ADD INDEX comp(id, modified,
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

Only one multi-valued key part can be used in a composite index. 复合索引中只能使用一个多值键部分。The multi-valued key part may be used in any order relative to the other parts of the key. 多值密钥部分可以相对于密钥的其他部分以任何顺序使用。In other words, the ALTER TABLE statement just shown could have used comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (or any other ordering) and still have been valid.换句话说，刚才显示的ALTER TABLE语句可能使用了comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified))（或任何其他排序），并且仍然有效。

Using multi-valued Indexes使用多值索引

The optimizer uses a multi-valued index to fetch records when the following functions are specified in a WHERE clause:当WHERE子句中指定以下函数时，优化器使用多值索引来获取记录：

We can demonstrate this by creating and populating the customers table using the following CREATE TABLE and INSERT statements:我们可以通过使用以下CREATE TABLE和INSERT语句创建和填充customers表来演示这一点：

mysql> CREATE TABLE customers (
    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     custinfo JSON
    ->     );
Query OK, 0 rows affected (0.51 sec)

mysql> INSERT INTO customers VALUES
    ->     (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    ->     (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    ->     (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    ->     (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    ->     (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

First we execute three queries on the customers table, one each using MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS(), with the result from each query shown here:首先，我们在customers表上执行三个查询，每个查询使用MEMBER OF()、JSON_CONTAINS()和JSON_OVERLAPS()，每个查询的结果如下所示：

mysql> SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

Next, we run EXPLAIN on each of the previous three queries:接下来，我们对前面三个查询中的每一个都运行EXPLAIN：

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

None of the three queries just shown are able to use any keys. 刚才显示的三个查询都不能使用任何键。To solve this problem, we can add a multi-valued index on the zipcode array in the JSON column (custinfo), like this:为了解决这个问题，我们可以在JSON列（custinfo）中的zipcode数组上添加一个多值索引，如下所示：

mysql> ALTER TABLE customers
    ->     ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

When we run the previous EXPLAIN statements again, we can now observe that the queries can (and do) use the index zips that was just created:当我们再次运行前面的EXPLAIN语句时，我们现在可以观察到查询可以（并且确实）使用刚刚创建的索引zips：

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

A multi-valued index can be defined as a unique key. If defined as a unique key, attempting to insert a value already present in the multi-valued index returns a duplicate key error. 多值索引可以定义为唯一键。如果定义为唯一键，尝试插入多值索引中已存在的值将返回重复键错误。If duplicate values are already present, attempting to add a unique multi-valued index fails, as shown here:如果已经存在重复值，则尝试添加唯一的多值索引失败，如下所示：

mysql> ALTER TABLE customers DROP INDEX zips;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE customers
    ->     ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips'
mysql> ALTER TABLE customers
    ->     ADD INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

Characteristics of Multi-Valued Indexes多值索引的特征

Multi-valued indexes have the additional characteristics listed here:多值索引具有以下列出的其他特征：

Limitations and Restrictions on Multi-valued Indexes多值索引的限制和限制

Multi-valued indexes are subject to the limitations and restrictions listed here:多值索引受以下限制和限制：

The MyISAM, InnoDB, NDB, and ARCHIVE storage engines support spatial columns such as POINT and GEOMETRY. MyISAM、InnoDB、NDB和ARCHIVE存储引擎支持POINT和GEOMETRY等空间列。(Section 11.4, “Spatial Data Types”, describes the spatial data types.) （第11.4节，“空间数据类型”描述了空间数据类型。）However, support for spatial column indexing varies among engines. 但是，对空间列索引的支持因引擎而异。Spatial and nonspatial indexes on spatial columns are available according to the following rules.空间列上的空间索引和非空间索引根据以下规则可用。

Spatial indexes on spatial columns have these characteristics:空间列上的空间索引具有以下特征：

Nonspatial indexes on spatial columns (created with INDEX, UNIQUE, or PRIMARY KEY) have these characteristics:空间列上的非空间索引（使用INDEX、UNIQUE或PRIMARY KEY创建）具有以下特征：

Following the key part list, index options can be given. 在关键部件列表之后，可以提供索引选项。An index_option value can be any of the following:index_option值可以是以下任意值：

ALGORITHM and LOCK clauses may be given to influence the table copying method and level of concurrency for reading and writing the table while its indexes are being modified. 在修改表的索引时，可能会给出ALGORITHM和LOCK子句来影响表复制方法和读写表的并发级别。They have the same meaning as for the ALTER TABLE statement. 它们与ALTER TABLE语句的含义相同。For more information, see Section 13.1.9, “ALTER TABLE Statement”有关更多信息，请参阅第13.1.9节，“ALTER TABLE语句”。

NDB Cluster supports online operations using the same ALGORITHM=INPLACE syntax used with the standard MySQL Server. NDB集群支持使用与标准MySQL服务器相同的ALGORITHM=INPLACE语法进行在线操作。See Section 23.5.11, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.更多信息，请参阅第23.5.11节，“NDB集群中ALTER TABLE的在线操作”。