mysql - 如何使用数据列表使用 FIND_IN_SET

Question

我以前多次使用过FIND_IN_SET，但这次有点不同。

早些时候我在表中搜索单个值，例如

SELECT * FROM tbl_name where find_in_set('1212121212', sku)

但现在我在表中有了要搜索的 SKU 列表。例如

'3698520147','088586004490','868332000057','081308003405','088394000028','089541300893','0732511000148','009191711092','752830528161'

我在表 SKU 中有两列 LIKE 081308003405 和 SKU Variation在 SKU 列中，我保存单个值，但在变体列中，我以逗号分隔格式保存值 LIKE 081308003405,088394000028,089541300893

SELECT * FROM tbl_name 
WHERE 1 
AND upc IN ('3698520147','088586004490','868332000057','081308003405','088394000028',
'089541300893','0732511000148','009191711092','752830528161')

我正在使用 IN 函数搜索 UPC 值，现在我还想在变体列中搜索变体。我关心的是如何使用变体列中的 SKU 列表进行搜索

现在，我必须在循环中检查 UPC 变体，这会花费太多时间。下面是查询

SELECT id FROM products 
WHERE 1 AND upcVariation AND FIND_IN_SET('88076164444',upc_variation) > 0

Answer 1

首先考虑以规范化的方式存储数据。这是一个很好的阅读:Is storing a delimited list in a database column really that bad?

现在 - 假设以下架构和数据:

create table products (
  id int auto_increment,
  upc varchar(50),
  upc_variation text,
  primary key (id),
  index (upc)
);
insert into products (upc, upc_variation) values
  ('01234', '01234,12345,23456'),
  ('56789', '45678,34567'),
  ('056789', '045678,034567');

我们想要找到具有变体 '12345' 和 '34567' 的产品。预期结果是第 1 行和第 2 行。

规范化模式 - 多对多关系

不是将值存储在逗号分隔的列表中，而是创建一个新表，该表将产品 ID 与变体对应起来:

create table products_upc_variations (
  product_id int,
  upc_variation varchar(50),
  primary key (product_id, upc_variation),
  index  (upc_variation, product_id)
);
insert into products_upc_variations (product_id, upc_variation) values 
  (1, '01234'),
  (1, '12345'),
  (1, '23456'),
  (2, '45678'),
  (2, '34567'),
  (3, '045678'),
  (3, '034567');

选择查询将是:

select distinct p.*
from products p
join products_upc_variations v on v.product_id = p.id
where v.upc_variation in ('12345', '34567');

如您所见 - 使用规范化模式，问题可以通过非常基本的查询来解决。我们可以有效地使用索引。

“利用”全文索引

在 (upc_variation) 上使用 FULLTEXT INDEX，您可以使用:

select p.*
from products p
where match (upc_variation) against ('12345 34567');

这看起来很“漂亮”并且可能很有效。但是，尽管它适用于这个示例，但我对这个解决方案感到不舒服，因为我不能确切地说它什么时候不起作用。

使用 JSON_OVERLAPS()

从 MySQL 8.0.17 开始，您可以使用 JSON_OVERLAPS() 。您应该将值存储为 JSON 数组，或者“即时​​”将列表转换为 JSON:

select p.*
from products p
where json_overlaps(
  '["12345","34567"]',
  concat('["', replace(upc_variation, ',', '","'), '"]')
);

没有索引可以用于此。但是 FIND_IN_SET() 也不能。

使用 JSON_TABLE()

从 MySQL 8.0.4 开始，您可以使用 JSON_TABLE() “动态”生成数据的规范化表示。同样，您可以将数据存储在 JSON 数组中，或者在查询中将列表转换为 JSON:

select distinct p.*
from products p
join json_table(
  concat('["', replace(p.upc_variation, ',', '","'), '"]'),
  '$[*]' columns (upcv text path '$')
) v
where v.upcv in ('12345', '34567');

这里不能使用索引。这可能是该答案中提出的所有解决方案中最慢的。

RLIKE/正则表达式

你也可以使用 regular expression :

select p.*
from products p
where p.upc_variation rlike '(^|,)(12345|34567)(,|$)'

参见 demo of all queries on dbfiddle.uk