两组具有逗号分隔值的MySQL交集 [英] Mysql intersection of two sets having comma separated value

问题描述

如果有人在mysql方面为我提供一些小帮助，那将非常有用.

It will be very great-full if anyone will provide me a small help in mysql.

我有一个具有10亿条记录的表，其中一列具有逗号分隔的值.

I have a table having 1 billion records in which one column having comma separated value.

我要用逗号分隔值以进行搜索.

I have a comma separated values to search.

我想选择那些在该逗号中具有任何值的行，并从该字符串值中分离出列.

I want to select those rows which having anyone value in that comma separated column from that string value.

例如 表是这样的，它具有以逗号分隔的列:-

e.g, Table is A having column comma_separated like this:-

并且我有一个字符串，其逗号分隔值为"79、62、70、107".

and i have a string having comma separated values "79, 62, 70, 107".

结果将是行号1,2,3,5,7,8,9,10(在提及图片中).

我使用正则表达式进行了此操作，但是这花费了太多时间，因此出于优化目的，我想避免这样做.

推荐答案

您无法真正优化自己的工作.基本上，您可以运行如下查询:

You can't really optimize what you are doing. Basically, you can run a query like this:

where find_in_set(79, comma_separated) > 0 or
      find_in_set(62, comma_separated) > 0 or
      find_in_set(70, comma_separated) > 0 or
      find_in_set(107, comma_separated) > 0

这需要全表扫描.而且，尽管性能可能比正则表达式稍好，但效率仍然不高.

This requires a full-table scan. And, although the performance might be slightly better than a regular expression, it still will not be efficient.

存储此数据的正确方法是作为连接表.这将乘以行数，因此数据的第一行将成为联结表中的三行(每个值一个).

The proper way to store this data is as a junction table. This multiplies the number of rows, so the first row in your data becomes three rows in the junction table (one for each value).

您不想将事物列表存储为逗号分隔的列表有许多原因.您的值看起来像另一个表中的id，这使情况变得更糟:

There are numerous reasons why you do not want to store lists of things as a comma-separated list. Your values look like ids in another table, making things even worse:

• 值应以其本机格式存储.因此，将整数存储为字符串不是一个好主意.
• SQL中列表的本机结构是表，而不是列表.
• 表上的函数更强大，并且具有字符串函数.
• SQL不能将索引(全文索引除外)用于字符串操作.
• 当您有一个引用另一个表的ID时，应该有一个外键约束.您不能使用存储在字符串中的列表来做到这一点.

这篇关于两组具有逗号分隔值的MySQL交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！