MySQL 选择具有匹配数组值的行

Question

我一直在寻找有关如何使用与另一个表中的另一个数组匹配某些值的数组来选择数据库中的行的信息。

我有一个名为“users”的表。表内名为“user_interests”的列包含一个包含兴趣的数组。一个例子可以是“食物、时尚、音乐”作为一种值(value)。

在另一个名为“posts”的表中，包含一个名为“post_keywords”的列，其值与“users”表中的另一个数组相同。

任何人都可以帮助我找到选择与用户兴趣匹配的帖子的正确方法吗？到目前为止我明白我必须在 MySQL 查询中执行此操作。

提前致谢。

Answer 1

在回答提出的问题之前，我们应该解决一个更重要的问题:我们是否真的应该将值存储在逗号分隔的列表中。这是 SQL 反模式。

<小时/>

<!-- 这个特定的 SQL 反模式在 Bill Karwin 的优秀著作的第 2 章中得到了很好的解释:SQL 反模式:避免数据库编程的陷阱，许多优秀的书商(包括 Amazon)都可以购买 p>

https://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557

-->

本章还讨论了更合适的 SQL 模式，将值存储为单独的行，每个值都位于其自己的行上。这就是 SQL 被设计来对行进行操作的方式。

<小时/>

考虑警告后，如果您仍然需要在字符串中以逗号分隔的值列表中搜索值...

MySQL 提供了一个 FIND_IN_SET 函数，它将返回列表中值的位置。

如果找到匹配值，该函数将返回一个正整数。

如果找不到匹配值，该函数将返回 0。如果任一参数为 NULL，该函数将返回 NULL。

作为该功能如何工作的简单演示:

  SELECT FIND_IN_SET('bowling' ,'food,fashion,music')    -- > 0
       , FIND_IN_SET('food'    ,'food,fashion,music')    -- > 1
       , FIND_IN_SET('fashion' ,'food,fashion,music')    -- > 2
       , FIND_IN_SET('music'   ,'food,fashion,music')    -- > 3

为了在查询中使用它，MySQL 允许我们在 bool 上下文中使用表达式...

  SELECT t.id
    FROM my_table t
   WHERE FIND_IN_SET('food'  ,t.my_comma_separated_list_col)
     AND FIND_IN_SET('music' ,t.my_comma_separated_list_col)

这将从 my_table 返回具有 my_comma_separated_list_col 值的行，例如:

'food,music'
'bowling,dancing,food,music'
'music,food'

如果在列表中找到指定的搜索值(例如“食物”)，FIND_IN_SET 将返回一个正整数，在 bool 上下文中计算结果为 TRUE。

如果在列表中找不到指定的值，FIND_IN_SET 将返回 0，其计算结果为 FALSE。

返回 NULL 只是一个 NULL，既不是 TRUE 也不是 FALSE。

<小时/>

注释:

NULL 值将导致 FIND_IN_SET 返回 NULL。

SELECT FIND_IN_SET(NULL  ,NULL)                        -- > NULL
     , FIND_IN_SET(NULL  ,'food,fashion,music')        -- > NULL 
     , FIND_IN_SET('food',NULL)                        -- > NULL

列表中的值的前导和尾随空格，或者将找不到正在搜索的值。除非 search 值和 list 值完全匹配(包括前导空格和尾随空格)。 (因此，不要在列表中的值之间使用任何空格):

值列表中的前导和/或尾随空格会导致“未找到”

SELECT FIND_IN_SET('bowling'  ,' food, fashion, music') -- > 0
     , FIND_IN_SET('food'     ,' food, fashion, music') -- > 0 !
     , FIND_IN_SET('fashion'  ,' food, fashion, music') -- > 0 !
     , FIND_IN_SET('music'    ,' food, fashion, music') -- > 0 !
;    
SELECT FIND_IN_SET('bowling'  ,'food ,fashion ,music ') -- > 0
     , FIND_IN_SET('food'     ,'food ,fashion ,music ') -- > 0 !
     , FIND_IN_SET('fashion'  ,'food ,fashion ,music ') -- > 0 !
     , FIND_IN_SET('music'    ,'food ,fashion ,music ') -- > 0 !
;

值中的前导或尾随空格会导致“未找到”

SELECT FIND_IN_SET('bowling'  ,'food,fashion,music')    -- > 0
     , FIND_IN_SET('food '    ,'food,fashion,music')    -- > 0 !
     , FIND_IN_SET(' fashion' ,'food,fashion,music')    -- > 0 !
     , FIND_IN_SET(' music '  ,'food,fashion,music')    -- > 0 !
;