获取mysql文本字段中的精确匹配计数

Question

我有一个带有文本字段的MySql表。

文本字段将被填充为类似于以下随机序列（可以是任何整数值）：

14,4,24,20,34,2

34,67,4,98,64

字符串（文本字段值）中没有前导或尾随逗号。

例如，我想要统计只有'4'的总出现次数。

对这两行进行查询应该返回2而不是7。

不确定如何编写这样的查询。

谢谢

Answer 1

对于MySQL 8.X

您可以使用REGEXP_REPLACE来找到4，从字符串中删除它们并计算长度的差异：

SELECT
    LENGTH(vals) - LENGTH(REGEXP_REPLACE(vals, '(?<=^|,)4(?=$|,)', ''))
FROM
    T;

这将返回每行中的“4”数量，然后您可以将它们全部相加：

SELECT
    SUM(LENGTH(vals) - LENGTH(REGEXP_REPLACE(vals, '(?<=^|,)4(?=$|,)', '')))
FROM
    T;

您可能还想使用AS来重命名这些值。

正则表达式的解释

(?<=^|,)4(?=$|,)是在寻找满足以下条件的“4”：

• 在逗号或字符串开始之前
• 在逗号或字符串结尾之后

对于旧版本的MySQL

查询非常丑陋，但您可以使用以下方法：

SELECT
    vals,
    LENGTH(Replace(Replace(Concat(',', vals, ','), ',4,', ',_,'), ',4,', ',_,')) - LENGTH(Replace(Replace(Replace(Concat(',', vals, ','), ',4,', ',_,'), ',4,', ',_,'), "_", "")) AS NB4
FROM
    test_table;

它首先用下划线（_）替换所有“4”的出现次数（例如不替换54中的4）。 然后，它计算带有这些下划线的字符串的长度减去不带下划线的字符串的长度，这就是您列表中的“4”的数量。

为什么使用这么多REPLACE？

在测试查询时，我发现MySQL的REPLACE函数的行为与我们期望的不同。以这个例子为例：4,4,4,4,4，如果使用单个Replace，我们期望它返回_,_,_,_,_。然而，它会原地替换逗号，如果逗号“匹配”两次，它不会计算第二次，这就是为什么需要2个REPLACE(..., ",4,", ",_,")的原因。