具有逗号分隔值的两组的Mysql交集

Question

如果有人能在 mysql 方面为我提供一点帮助，那就太好了。

我有一个包含 10 亿条记录的表，其中一列的值以逗号分隔。

我有一个逗号分隔的值要搜索。

我想从该字符串值中选择那些在逗号分隔列中具有任何值的行。

例如，表是 A 的列以逗号分隔，如下所示:-

我有一个以逗号分隔的值“79, 62, 70, 107”的字符串。

结果将是行号 1,2,3,5,7,8,9,10(在提到的图像中。)

我是用正则表达式做的，但它花费了太多时间，所以为了优化目的我想避免这种情况。

Answer 1

您无法真正优化您正在做的事情。基本上，您可以像这样运行查询:

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

这需要全表扫描。而且，虽然性能可能比正则表达式稍微好一些，但它仍然不是高效的。

存储此数据的正确方法是作为联结表。这会乘以行数，因此数据中的第一行在联结表中变为三行(每个值一行)。

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

• 值应以其原始格式存储。因此，将整数存储为字符串不是一个好主意。
• SQL 中列表的原生结构是表，而不是列表。
• 表格函数和字符串函数更强大。
• SQL 不能使用索引(全文索引除外)进行字符串操作。
• 当您有一个引用另一个表的 ID 时，您应该有一个外键约束。您不能对存储在字符串中的列表执行此操作。