MYSQL select all record if contains specific number

I have a small problem, I have a table like this:

id|name|group|date_created
1|Volvo|1,3|06-04-2020 10:00:00
2|Audi|3|06-04-2020 10:00:00

etc….

Now I wish I could get all the records that have the value 1 inside the group column.

I tried LIKE “%1%”, but I don’t think it’s a good query. Can you address me?

SELECT id FROM cars WHERE group LIKE '%1%' 

Answer

The problem with your query is that it would wrongly match '1' against a list like '45,12,5' for example.

One method is to add commas on both ends before searching:

where concat(',', `group`, ',') like '%,1,%';

But in MySQL, it is much more convenient to use string function find_in_set(), whose purpose is just what you are looking for, ie search for a value in a comma-separated list:

select id from cars where find_in_set('1', `group`) > 0

Notes:

• you should fix your data model, and have a separated table to store relationship between ids and groups, with each tuple on a separate row. Related reading: Is storing a delimited list in a database column really that bad?

• group is a reserved word in MySQL, so not a good choice for a column name (you would need to surround it with backticks everytime you use it, which is error-prone)