MySQL Search Query на двух различных полях

При установке двух столбцов CONCAT() функция LIKE становится чувствительной к регистру. Таким образом, это должно найти результаты, но не оптимально для производительности:

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

Это позволяет MySQL выполнять работу над каждой строкой.

Вам нужно переформулировать выражение concat в предложении where.

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

К сожалению, "as" просто создает псевдоним столбца, а не переменную, которую можно использовать в других местах.

Главное

Убедитесь, что у вас есть составной индекс на first_name и last_name. В противном случае очень легко в конечном итоге выполнить полное сканирование таблицы, независимо от того, как вы к этому подойдете. Поэтому, если у вас еще нет индекса, создайте его:

CREATE INDEX users_firstandlast ON users(first_name, last_name);

Опции синтаксиса

После того как индекс создан, у вас есть несколько вариантов:

Вариант 1: Как сказал Willis Blackburn, повторите CONCAT в предложении WHERE (потому что AS не создает имя, которое можно использовать в предложении WHERE):

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

Используйте EXPLAIN для проверки в вашей конкретной ситуации, но в моих тестах говорится, что используется составной индекс, даже если вы используете функцию в предложении WHERE.

Вариант 2: В этом конкретном случае вы всегда можете просто использовать два LIKE s в предложении 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, и протестируйте, в вашей конкретной среде.