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
- Truncate a Foreign Key Constrained Table
- Add Days to a Datetime
- Find Value in Column with Comma-Separated Values
- Calculate Number of Days Between Two Dates
- Get the Last Day of the Month (Coming soon)
- Get the First Day of the Month (Coming soon)
- Select Rows With Date Within a Month (Coming soon)
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!