商家在后臺系統中根據商品名稱檢索商品，在商品數量有限時，可以使用模糊查找方式實現簡單地檢索。如使用 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}%'

這種方式并不準確，例如模版 A 的related_product_ids = 110,111,112, 當用戶輸入 11 時，模板 A 會被檢索出來。

如何優化呢？可以將related_product_ids 存儲為 **"110,111,112,"**，增加 1 個逗號。  SQL語句調整為

WHERE related_product_Ids like  '%${inputProductId},%'

此種方式就萬無一失了嗎？不然，當用戶輸入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;

2）創建一條 JSON 記錄

insert into  coupon_template(related_product_ids) values ('[110,111,112]');

3）對 JSON 類型創建多值索引

alter table coupon_template add INDEX `relatedProductIdsIndex`((cast(json_extract(`related_product_ids`, '$[*]') as unsigned array)));

創建多值索引部分，需要指定表達式。范式是 ((cast(json_extract(columnName, 'expression') as unsigned array)))

因為 product_ids是 Json 數組，所以表達式部分 是 $[*]。 如果是一個 Json Object，例如如下的 JSON

{
  "name": "xxxx",
  "ids": [110,111,112]
}

表達式應該為 $.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)));

如何查詢多值索引

使用 MEMBER OF (表達式) 可查詢多值索引，例如以下示例

圖片

select * from coupon_template WHERE 110 MEMBER OF (ids_ext-> '$.ids');

使用 explain 查看執行計劃，如下截圖所示，確實可以使用到 ids_ext_index 索引。

圖片

最后

MySQL 8.0 在 2018 年發布，目前已經成熟。該版本引入了一系列新特性如 JSON類型、窗口函數、多值索引和OnlineDDL Instant瞬時改表等。

此外官方已經在 2023 年底停止 MySQL 5.7版本，所以還是要盡快遷移到 8.0 版本。