MySQL search query in two different fields

Question

I need to search in two fields using the LIKE function and match also in reverse order. My table uses InnoDB, which does not have full-text search.

Consider the following case:

I have a user table named first_name and last_name. It has a line with the following value:

{
    first_name: 'Ludwig',
    last_name: 'van Beethoven',
}

Cases:

• You can find "Ludwig van Beethoven"
• You can find Beethoven Ludwig
• You can find "Ludwig"
• You can find Beethoven.

I tried this SQL statement but no luck.

SELECT CONCAT(first_name, ' ', last_name) as fullname 
  FROM users 
 WHERE fullname LIKE '%Ludwig van Beethoven%';

Answer 1

You need to re-specify the concat expression in the where clause.

 SELECT CONCAT(first_name, ' ', last_name) as fullname 
      FROM users 
     WHERE CONCAT(first_name, ' ', last_name) LIKE '%doe%';

, "" , , .

Answer 2

, first_name last_name. , , , . , , :

CREATE INDEX users_firstandlast ON users(first_name, last_name);

, :

1. , CONCAT WHERE ( AS doesn ' t , WHERE):

SELECT CONCAT(first_name, ' ', last_name) as fullname 
FROM users 
WHERE CONCAT(first_name, ' ', last_name) LIKE '%doe%';

EXPLAIN, , , , re, WHERE.

2. LIKE WHERE:

SELECT CONCAT(first_name, ' ', last_name) as fullname 
FROM users 
WHERE first_name LIKE '%doe%' or last_name LIKE '%doe%';

, ( first_name last_name) - , , EXPLAIN [ , ], ).

3 , , HAVING . MySQL , HAVING , , . , EXPLAIN , , , HAVING , . , . HAVING MySQL ( ), , CONCAT, , MySQL.:-) , .

, , 2, ; 1, ( ) HAVING, ( , , ). , EXPLAIN .

Answer 3

SELECT * FROM users WHERE CONCAT (first_name, '', last_name) LIKE '% Ludwig%' ​​CONCAT (last_name, '', first_name) LIKE '% Ludwig%';

, " ".

Answer 4

When you CONCAT () two columns, LIKE become case sensitive. Thus, this should find results, but not optimal for performance:

SELECT CONCAT(first_name, ' ', last_name) AS fullname FROM users 
WHERE LOWER(CONCAT(first_name, ' ', last_name)) LIKE LOWER('%doe%');

This allows MySQL to work on every row.