mysql if clause maybe?

[FrEaKmAn]

Hello,

what I want to do is that I'm able to assign for example artist to more then one category. I know I could do this with creating additional table, but for my case 95% artists will belong to only 1 category, while other 5% to more the one and I don't want to fill mysql db for nothing..

I found IN clause but not exactly that I want. If we have a field category and I input category ids like 14,15, can I use some command to separate string into arrays and check them with SQL?

+---------+--------+----------------------+
| id      | artist | category             |
+---------+--------+----------------------+
| 1       | John   | 14,15                |
+---------+--------+----------------------+

I tried SELECT * FROM artists WHERE category IN (14,15), well it does work and if I search only 14 it works, if 15 then nothing comes out...

[oracle128]

It would definitely be worth using a separate table for this, even if only 1 of your artists has more than one category. You should be aware that having multiple values in a single field is a denormalized solution.
It is possible to work with it, but not necessarily desirable.

IN is not meant for what you want. IN is for searching for one of several possible values. IN is essentially a shorthand way of writing multiple OR conditions.

SELECT * FROM table WHERE value IN (1,2,3)

is equivalent to

SELECT * FROM table WHERE value=1 OR value=2 OR value=3

Separating comma separate values into string arrays (aka tokenizing) cannot be done in SQL, as it is neither ANSI SQL functionality, nor does any common database implement it. You could implement it in PL/SQL if you wanted. MySQL supports Functions and Procedures.

With plain SQL, you have two base options: LIKE or INSTR. Both solutions are similar:

SELECT * FROM artists WHERE category LIKE '%14%'

SELECT * FROM artists WHERE INSTR(category,'14')>0

Both do the same thing, and take about the same amount of time to process: LIKE is desirable because it's ANSI SQL.
But they both have the same flaw: if you have an artist in category '140', searching for category '14' with the above statements will return that artist (because '14' is in the string '140').

The best way to avoid this is to be sure every category is surrounded by delimiters (in this case, commas) on both sides. So instead of '14,15' you have ',14,15,'. This way, you can include the surrounding commas in the search, and be sure you're getting the whole value:

SELECT * FROM artists WHERE category LIKE '%,14,%'

will return artists in category 14, but not 140.

In fact, you don't even have to store the commas in the database, you can concatenate the strings:

SELECT * FROM artists WHERE CONCAT(',',category,',') LIKE '%,14,%'

Here's what it would look like if you normalized the database:

artists
+---------+--------+
| id      | artist |
+---------+--------+
| 1       | John   |
+---------+--------+

categories
+---------+----------------------+
| id      | category             |
+---------+----------------------+
| 1       | sculptor             |
+---------+----------------------+
| 2       | painter              |
+---------+----------------------+

artistcats
+-----------+-------------+
| artistid  | categoryid  |
+-----------+-------------+
| 1         | 1           |
+-----------+-------------+
| 1         | 2           |
+-----------+-------------+

(Primary key in artistcats would be a joint key of artistid and categoryid; or could be a separate Id field)