别再滥用模糊查找了，来试试 MySQL 多值索引！-益强资讯优选

商家在后台系统中根据商品名称检索商品，别再在商品数量有限时，滥用可以使用模糊查找方式实现简单地检索。模糊如使用 WHERE columName like %inputName% 实现模糊查找。查找

然而有一种场景正在滥用模糊查找。试试

在优惠券配置后台中，值索需要具备优惠券模版检索能力，别再其中模板检索条件是滥用根据商品 ID，检索哪些优惠券模版关联了指定商品。模糊一个优惠券模版可以在多个商品上使用，查找两者关联关系为 1:N；

业界通常的试试实现方案有两种：

1）使用 ElasticSerach实现全文检索。2）使用 MySQL 检索。值索

当数据量较少，别再检索条件有限时，滥用可优先先选择方案更加简单地MySQL方案。模糊

如何实现指定商品 ID 检索关联的优惠券模版功能呢？MySQL 有两种方案

方案 1：模糊查找

通过模糊查找实现检索，如 coupon_template 中 relatedProductIds 属性记录了关联的商品 Id 列表。

复制WHERE related_product_Ids like %${inputProductId}%1.

这种方式并不准确，例如模版 A 的related_product_ids = 110,111,112, 当用户输入 11 时，模板 A 会被检索出来。

如何优化呢？可以将related_product_ids 存储为 **"110,111,112,"**，增加 1 个逗号。 SQL语句调整为

复制WHERE related_product_Ids like %${inputProductId},%1.

此种方式就万无一失了吗？不然，当用户输入12 时，依然会错误地检索到模版 A。

再次优化后，related_product_ids 存储为 ",110,111,112," 即前后均添加逗号，云服务器SQL 语句调整为

WHERE related_product_Ids like %,${inputProductId},% 前后添加逗号，用来截断。

方案 2：多值索引查找

MySQL 8.0版本以后，可以建立多值索引，应用此种查询场景。相比模糊查找方案，多值索引的检索性能更好，方案更加简单。

什么是多值索引

对普通的索引来说，每一条记录仅对应一条索引记录，对多值索引而言，一条记录可以对应多条索引记录。

普通索引可以对 user_id、order_id 字段建立索引。多值索引可以对 JSON 字段建立索引，例如 related_product_Ids 字段在数据库中是 JSON 类型，值为 [110, 111, 112]，当对该字段建立多值索引时，可以建立三条索引记录，110，111，112 ，因此单独查询任一商品Id，均可以索引到记录 A。

接下来查看下如何使用多值索引

如何使用多值索引

1）创建JSON 类型的字段复制alter table coupon_template add column related_prodcut_ids JSON default NULL;1. 2）创建一条 JSON 记录复制insert into coupon_template(related_product_ids) values ([110,111,112]);1. 3）对 JSON 类型创建多值索引复制alter table coupon_template add INDEX `relatedProductIdsIndex`((cast(json_extract(`related_product_ids`, $[*]) as unsigned array)));1.

创建多值索引部分，需要指定表达式。服务器托管范式是 ((cast(json_extract(columnName, expression) as unsigned array)))

因为 product_ids是 Json 数组，所以表达式部分是 $[*]。如果是一个 Json Object，例如如下的 JSON

复制{ "name": "xxxx", "ids": [110,111,112] }1.2.3.4.

表达式应该为 $.ids

例如下面的代码示例，创建了 ids_ext JSON 属性，对 json 中的 ids属性建立二级索引。注意表达式变成了，ids_ext-> $.ids，这是创建二级索引的另一种简洁写法，无需嵌套 json_extract 方法了。

复制alter table coupon_template add INDEX ids_ext_index ((CAST(ids_ext-> $.ids AS UNSIGNED ARRAY)));1.