如何使用数据列表使用FIND_IN_SET [英] How to use FIND_IN_SET using list of data

问题描述

我之前曾多次使用FIND_IN_SET，但是这种情况有些不同.

I have used FIND_IN_SET multiple times before but this case is a bit different.

我以前在表中搜索单个值

Earlier I was searching a single value in the table like

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

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

But now I have the list of SKUs which I want to search in the table. E.g

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

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

I have two columns in the table SKU LIKE 081308003405 and SKU Variation In SKU column I am saving single value but in variation column I am saving the value in the comma-separated format 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列表进行搜索

I am using IN function to search UPC value now I want to search variation as well in the variation column. This is my concern is how to search using SKU list in variation column

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

For now, I have to check in the loop for UPC variation which is taking too much time. Below is the query

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

推荐答案

首先考虑以标准化方式存储数据.这是一本好书:正在存储数据库列中的分隔列表真的那么糟糕吗?

First of all consider to store the data in a normalized way. Here is a good read: Is storing a delimited list in a database column really that bad?

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

Now - Assumng the following schema and data:

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'变体的产品.预期的结果是第一行和第二行.

We want to find products with variations '12345' and '34567'. The expected result is the 1st and the 2nd rows.

创建一个新表，而不是将值存储在以逗号分隔的列表中，该表将映射具有变化的产品ID:

Instead of storing the values in a comma separated list, create a new table, which maps product IDs with variations:

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');

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

As you see - With a normalized schema the problem can be solved with a quite basic query. And we can effectively use indices.

使用(upc_variation)上的全文索引，您可以使用:

With a FULLTEXT INDEX on (upc_variation) you can use:

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

这看起来很漂亮"，可能很有效.但是，尽管它适用于本示例，但我对这种解决方案感到不满意，因为无法确切地说出什么时候它不起作用.

This looks quite "pretty" and is probably efficient. But though it works for this example, I wouldn't feel comfortable with this solution, because I can't say exactly, when it doesn't work.

从MySQL 8.0.17开始，您可以使用

Since MySQL 8.0.17 you can use JSON_OVERLAPS(). You should either store the values as a JSON array, or convert the list to JSON "on the fly":

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

不能为此使用索引.但是FIND_IN_SET()都不能.

No index can be used for this. But neither can for FIND_IN_SET().

自MySQL 8.0.4起，您可以使用 JSON_TABLE ()来实时"生成数据的规范化表示.再次在这里，您可以将数据存储在JSON数组中，或者在查询中将列表转换为JSON:

Since MySQL 8.0.4 you can use JSON_TABLE() to generate a normalized representation of the data "on the fly". Here again you would either store the data in a JSON array, or convert the list to JSON in the query:

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');

此处无法使用索引.这可能是此答案中提出的所有解决方案中最慢的一种.

No index can be used here. And this is probably the slowest solution of all presented in this answer.

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

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

请参见关于dbfiddle.uk的所有查询的演示

这篇关于如何使用数据列表使用FIND_IN_SET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！