CODEWITHSUNDEEP
mysqlsql
Jens Kvist
Jens Kvist

Reputation: 559

MySQL Selecting Rows with Matching Array Values

I have been searching around for a bit about how I could select the rows inside my database with an array that matches some values with another array in another table.

I have a table called "users". Inside the table a column named "user_interests" contains an array with interests. An example could be "food, fashion, music" as a value.

In another table called "posts" contains a column named "post_keywords" with the value of another array like inside the "users" table.

Can anyone help me with find the right way of selecting the posts that matches with the users interests? I understand so far that I have to do this in the MySQL query.

Thanks in advance.

Upvotes: 0

Views: 2505

Answers (1)

spencer7593
spencer7593

Reputation: 108400

Before we answer the question that was asked, we should address a more important question: whether we should really be storing values in comma separated lists. That's a SQL antipattern.

<!-- That particular SQL Antipattern is explained pretty well in Chapter 2 of Bill Karwin's excellent tome: SQL Antipatterns: Avoiding the Pitfalls of Database Programming available from many fine booksellers, including Amazon

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

-->

The chapter also addresses a more appropriate SQL Pattern, storing values as separate rows, each value on its own row. That's how SQL was designed to operate... on rows.

After considering the warnings, if you still have a need to search for values in a comma separated lists of values in a string...

MySQL provides a FIND_IN_SET function, which will return the position of a value within a list.

If a matching value is found, the function will return a positive integer.

If a matching value is not found, the function will return 0. If either of the arguments is NULL, the function will return NULL.

As a simple demonstration of how the function works:

  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

To make use of that in a query, MySQL allows us to use an expression in a boolean context...

  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)

This would return rows from my_table that had values in my_comma_separated_list_col such as:

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

If the specified search value (e.g. 'food') is found in the list, FIND_IN_SET returns a positive integer, which evaluates to TRUE in a boolean context.

If the specified value is not found in the list, FIND_IN_SET returns a 0, which evaluates to FALSE.

A return of NULL is just a NULL, neither TRUE nor FALSE.

NOTES:

A NULL value will cause FIND_IN_SET to return NULL.

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

Leading and trailing spaces in the values in the list, or the value being searched for will not be found. Unless both the search value and the list value are an exact match including leading and trailing spaces. (So, just don't use any spaces between the values in the list):

Leading and/or trailing spaces in the list of values results in "not found" 

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 !
;

leading or trailing spaces in values cause "not found"

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 !
;

Upvotes: 1

Related Questions