时间戳

首页 > 解决方案 > 如何在所有组合中获取逗号分隔的记录

mysql - 如何在所有组合中获取逗号分隔的记录

问题描述

我有一张表由 5 个字段组成，例如

|id|platform|sites  |path |date
|1 |0       |1,2,3  |//ab |1/2/2019
|2 |0       |1,2    |//ab |1/2/2019
|3 |0       |1,2,3,4|//ab |1/2/2019
|4 |0       |2,3,1  |//ab |1/2/2019

我想在“网站”列上搜索表格，但该字段值会有任何组合。例如 2,3,1 或 3,1,2 等，结果应该是上面例子中的 id 1 和 2。我还需要精确长度的结果作为搜索值。如果用户输入 3 个逗号分隔值，那么我想要包含精确 3 个逗号分隔值但在所有组合中的结果。

我尝试了“IN”和“LIKE”子句，但它不适用于所有组合和精确长度搜索

IN 子句 select * from tbl_demo where sites in (1,2,3) order by 1 DESC;

LIKE 子句 select * from tbl_demo 其中像 "%1,2,3%" 这样的网站按 1 DESC 排序；

标签： mysql

解决方案

显然，从数据库规范化的角度来看，将一串数字存储在逗号分隔的列表中是错误的。我看到其他人试图将问题作为“在数据库列中存储分隔列表真的那么糟糕吗？”的副本来结束。但这不是重复的，因为它拒绝了 OP 的问题而不是回答它。

让我们讨论解决这个问题的选项。

第一个选择是适当地对其进行规范化。创建一个子表并在您显示的表中存储对 的引用id，加上每行一个站点值。

| id | site |
|  1 |    1 |
|  1 |    2 |
|  1 |    3 |
|  2 |    1 |
|  2 |    2 |
...

然后您可以进行分组计数，或者至少按排序顺序执行 GROUP_CONCAT() 以便您可以与您的标准进行比较。

SELECT id, GROUP_CONCAT(site ORDER BY site) AS sites
FROM MyTable GROUP BY id
HAVING sites = '1,2,3'

如果您无法更改数据的组织，那么如果您可以修复字符串以使数字始终按升序排列，将会有很大帮助。但你可能做不到（或者不想尝试）。

另一种选择是使用某些人用来搜索逗号分隔字符串的 FIND_IN_SET() hack 一次比较一个值。它不是为此目的而设计的，它是一个与 MySQL 的SET 数据类型一起使用的函数，但它也适用于字符串，因此被逗号分隔的数字字符串困扰的人使用它。

SELECT ...
FROM YourTable
WHERE FIND_IN_SET('1', sites)
  AND FIND_IN_SET('2', sites)
  AND FIND_IN_SET('3', sites)

您需要与要搜索的数字一样多的术语。但是您还需要确保该集合不包含更多数字。

  AND LENGTH(sites) - LENGTH(REPLACE(sites, ',', '')) + 1 = ?

在这个表达式中，?是我们正在寻找的元素数量：3。

这种类型的解决方案可能有效，但无法优化。它总是会进行表扫描，所以它会变得越来越慢，你的表越大。

推荐阅读