Is there better way to use LIKE for multiple values in MYSQL?

Question

Say there's a table user as below:

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | char(64)         | NO   |     | NULL    |                |
| img_hash | varchar(32)      | NO   |     |         |                |
+----------+------------------+------+-----+---------+----------------+

And I want to make a fuzzy match on field name, and there are multiple names in a list pending match as:

["name1", "name2", "name3", ...]

I'm tring to do with :

select 
    id, name, img_hash 
from
    user
where name like "%name1%" 
or name like "%name2%"
or name like "%name3%"
...

If there better way to do a fuzzy matching work in this problem?

Thanks.

Answer 1

You can try REGEXP

SELECT ID, NAME, IMG_HASH FROM USER 
WHERE NAME REGEXP 'name1|name2|name3'

More simpler version would be something like below. You can copy paste the names directly inside CONCAT_WS

SELECT ID, NAME, IMG_HASH FROM USER 
WHERE NAME REGEXP CONCAT_WS("|","name1", "name2","name3");

CHECK DEMO HERE

Please note that this may be slower than using LIKE

Answer 2

Levenstein distance - How many changes required to convert one to another.

Soundex() - The SOUNDEX function converts a phrase to a four-character code. The first character is the first letter of the phrase. Vowels are omitted unless the first letter of the phrase is a vowel and the other three characters represent the rest of the phrase

Difference() - Evaluates two expressions and assigns a value between 0 and 4, with 0 being little to no similarity and 4 representing the same or very similar phrases. This value is derived from the number of characters in the SOUNDEX of each phrase that are the same

Comparison