We recently ran into a challenge with a database design. The database schema includes multiple tables. The tables have foreign key references between each other. One of the tables uses a column that contains a comma-separated list of values where each value is a reference to a row in a database table.

We can’t change the table design and de-normalize the column with comma-separated values. We had to use the column and the included values “as is”.

This tutorial shows you how to find values in a MySQL column with comma-separated values.

MySQL Series Overview

Context for the Problem

The challenge we ran into with this database column was related to searches for references with specific IDs. We couldn’t use a LIKE query, because a search for id 10 would match rows with references to IDs 100, 101, 110, and so on.

Here’s a database table design containing references to other rows. The references column is a comma-separated list of IDs:

| id | title             | references   |
|----|-------------------|--------------|
| 1  | Some random Title | 10,313,2000  |
| 2  | Another title     | 111,515      |

The task is to find matching references for a given ID. The next section describes our approach to handling the search in MySQL.

Use MySQL’s FIND_IN_SET String Function

MySQL has a FIND_IN_SET string function. This FIND_IN_SET(str, str_list), function takes a search value as the first argument and the column in which to search for the value as the second argument. We’re using this FIND_IN_SET function to find all rows that match our search term.

Here’s a sample SQL query that finds all rows with references to ID 10:

SELECT *  
FROM TABLENAME  
WHERE FIND_IN_SET('10', references)

-- matches only row with ID 1

You’re probably using a dynamic search value instead of the hardcoded value 10. You should provide the search value as a string. We don’t know the implementation details of your SQL library, but it’s likely handling the string cast for you.

Handling Spaces Around Values in a Comma-Separated List

The example above has a comma-separated list of values where each value comes directly after a comma. The values don’t have spaces around them. If your column values have spaces, you may need to remove them from the comma-separated values in the related column.

The values in a table may look like this:

| id | title             | references     |
|----|-------------------|----------------|
| 1  | Some random Title | 10, 310, 1000  |
| 20 | Another title     | 110, 510       |

You can remove spaces in MySQL using the REPLACE(str, from_str, to_str) function. The REPLACE function is another MySQL function keeping and handling your work in the database.

The refined SQL statement to find values in a comma-separated list with spaces looks like this:

SELECT *  
FROM TABLENAME  
WHERE FIND_IN_SET('10', REPLACE(references, ' ', ''))

-- matches only row with ID 1

The refined SQL query uses the combination of FIND_IN_SET and REPLACE and does all the work in the database layer.

Use a MySQL LIKE Query

A naive approach could be an SQL query using a LIKE comparison. The problem with a LIKE query in this case is that you can’t search for the exact term. The references column may contain values that partially contain the search text. Yet, you can still use a LIKE query in combination with a programmatic filter afterward.

Let’s say we’re looking for rows referencing ID 10:

| id | title             | references     |
|----|-------------------|----------------|
| 1  | Some random Title | 10, 310, 1000  |
| 20 | Another title     | 110, 510       |

First, search for all rows having references to ID 10 using a LIKE query:

SELECT *  
FROM TABLENAME  
WHERE references LIKE '%10%'

-- matches both rows, because the text `10` is present in all references

Then, you should filter the results list and apply the exact match. Your SQL library returns an array of rows and you can filter the list returned from the SQL query with the LIKE query. Here’s a sample code outlining the idea of querying the database with a like query and then filtering the items down to the exact matches:

const searchId = 10

const allResults = await sql.query(`  
  SELECT *
  FROM TABLENAME
  WHERE references LIKE '%${searchId}%'
`)
// `allResults` could include items that partially matched
// the search value. You need to ensure only to keep the
// matches that exactly match the search term. Filter!


const results = allResults.filter(item => {  
  return item.references.includes(searchId)
})
// contains only the row with ID 1 (referencing ID 10)

This approach needs more compute resources because it might return more database rows than needed and you need to filter the result list. If possible, go with the FIND_IN_SET approach.

Enjoy searching MySQL!

---

Mentioned Resources

• MySQL FIND_IN_SET string function
• MySQL REPLACE(str, from_str, to_str) function