时间戳

首页 > 解决方案 > 如何使用 FIND_IN_SET 使用数据列表

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

问题描述

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

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

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

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

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

我在表SKULIKE 081308003405中有两列，SKU Variation 在 SKU 列中我保存单个值，但在变体列中我以逗号分隔格式保存值 LIKE081308003405,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

标签： mysql

解决方案

首先考虑以标准化的方式存储数据。这是一本很好的读物：在数据库列中存储分隔列表真的那么糟糕吗？

现在 - 假设以下模式和数据：

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'. 预期结果是第一行和第二行。

规范化模式 - 多对多关系

不要将值存储在逗号分隔的列表中，而是创建一个新表，该表将产品 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');

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

“利用”全文索引

使用 FULLTEXT INDEX(upc_variation)可以使用：

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 / 正则表达式

您还可以使用正则表达式：

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

请参阅dbfiddle.uk 上所有查询的演示

推荐阅读