Mysql: how to find if column value in a list of strings

Question

Mysql: how to find if column value in a list contains many strings. Expl: t.name=‘test’

Select * 
from table t 
where ( t.name Like [‘test tuto1’, ‘test tuto2’, ‘test tuto3’, ‘teeeest’....]

I have to get * with t.name contains the first 3 exemples result

Answer 1

There are several option for this :

1) build a series of ORed expressions like :

SELECT t.* from mytable t WHERE t.name LIKE '%val1%' or t.name LIKE '%val2%'...

2) Use find_in_set : probably more efficient than combined LIKEs

SELECT t.* from mytable t WHERE find_in_set(t.name, 'val1,val2,...')

3) Use a regexp : same order of speed as find_in_set

SELECT t.* from mytable t WHERE t.name REGEXP 'val1|val2|...';

4) If you have a very large list of values, your query will slow down and you would eventually hit the max size of a varchar in the expression that represents the list. I would recommend stuffing them in a database table and the use a WHERE EXIST condition with a correlated subquery :

SELECT t.*
FROM mytable t
WHERE EXISTS (SELECT 1 from mylist where t.name like CONCAT('%', l.val '%'))

5) with MySQL 8.0, the list table can be emulated with a CTE :

WITH mylist AS (
    SELECT '%val1%' UNION SELECT '%val2%' UNION ...)
)
SELECT t.*
FROM mytable t
WHERE EXISTS (SELECT 1 from mylist where t.name like CONCAT('%', l.val '%'))

Answer 2

Just came across this post. What about:

SELECT t.*
FROM mytable t
WHERE t.name IN ('name1,name2,name3,name4,...')

I am sure the FIND_IN_SET and REGEX solutions are way more performant but I didn't want to let the IN operator unmentioned.