取得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,", ",_, ")的原因。