How is a comma-separated value from a subquery interpreted in an IN() clause?

Databases
1

I have a table called system_vars that stores among other things a comma-separated list of integers - ‘66,55,44,33’ etc.

If I use that field in a subquery inside of an IN() statement, is there a way to have the result interpreted as individual integers instead of a single string?

Example:

SELECT * FROM products WHERE category_id NOT IN(SELECT sys_val FROM system_vars WHERE sys_var = ‘excluded_cats’);

  • Please do not say “Normalize the table”. I know it’s not the best design, but it’s what I was handed and changing it is not possible at the moment.

EDIT: But I would be OK if the solution involved creating a normalized temp table. I just wasn’t sure how to split the value using only MySQL commands.

PS: The final solution needs to be usable in a view.

2

you can split the data in two ways, one of them very clumsy using CONCAT with commas and LIKE with commas, the other relatively clean using the FIND_IN_SET function

but both of those methods do not scale, as they always require a table scan

normalizing the data would result in searches that are optimized because they can use an index

table scans are grossly inefficient

anyhow, you asked me not to tell you to normalize, so i won’t

:slight_smile: