дополнительные поисковые параметры в запросе SQL и строках с нулевыми значениями

Вы всегда можете вообще запретить нулевые значения - в конце концов, все должны быть живы в течение некоторого положительного числа лет и находиться где-то географически.

Если вы абсолютно должны разрешить нули, используйте либо COALESCE , как предлагали другие, либо, возможно, IFNULL , который специфичен для MySQL и может быть немного более оптимальным, поскольку занимает ровно 2 параметры.

Вы можете использовать COALESCE :

select firstname, lastname, ....
from profiles
where (coalesce(firstName, '') like '%{firstname}%')
AND (coalesce(lastName, '') like '%{lastName}%')

Это фундаментальное свойство NULL : при сравнении его с любым , включая NULL , он возвращает false , что является почему то, что вы делаете, не работает.

Итак, первый вопрос, на который нужно ответить: почему вы хотите, чтобы строка с NULL lastname возвращалась, когда они вводят lastname of "smith"? Возможно, вы имеете в виду, что либо имя , либо фамилия должны совпадать для возврата, и в этом случае вы выполняете неправильный запрос. Самое наивное решение:

SELECT firstname, lastname, ....
FROM profiles
WHERE IFNULL(firstName LIKE'%{firstname}%'
OR lastName LIKE '%{lastName}%'

Теперь это будет работать для нескольких сотен и, возможно, нескольких тысяч строк, но не будет масштабироваться дальше этого по нескольким причинам:

1. OR обычно плохо с точки зрения производительности. По возможности избегайте их. Если вы посмотрите на приложения баз данных, написанные опытными программистами, вы, вероятно, не найдете ни одного условия OR (за исключением тех, кто пропагандирует достоинства OR на фоне того, что однажды написал приложение гостевой книги, у которого 3 пользователя и 2 обращения в месяц);
2. Выполнение LIKE с % впереди будет означать, что никакие индексы не будут предъявлены иски.

Есть несколько решений (2). Вероятно, самым простым в MySQL является использование полнотекстового поиска в таблицах MyISAM. Он не найдет совпадений, таких как «Johannes», если вы введете «han», но обычно этого достаточно.

Часто вы имеете дело с условиями ИЛИ , используя либо UNION , либо (предпочтительно) UNION ALL в нескольких запросах.Например:

SELECT firstname, lastname, ....
FROM profiles
WHERE firstName LIKE'%{firstname}%'
AND lastName LIKE '%{lastName}%'
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName LIKE'%{firstname}%'
AND lastname IS NULL
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName IS NULL
AND lastName LIKE '%{lastName}%'
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName IS NULL
AND lastName IS NULL

Выглядит большим и некрасивым, но UNION ALL очень хорошо масштабируется (игнорируя % в начале критерия LIKE ). По сути, он объединяет (в данном случае) четыре запроса. UNION будет выполнять неявное DISTINCT в строках результатов, но мы знаем, что здесь не будет перекрытия, потому что мы меняем проверку на NULL .

Другая возможность - не рассматривать NULL как нечто, что вы хотите найти. Это гораздо лучшее и интуитивно понятное решение (имхо). Если кто-то набирает фамилию «Смит», вы действительно хотите, чтобы отображались строки с NULL фамилией?

Или вы хотите, чтобы они отображались, потому что у вас могло быть совпадение по первому имя? Если да, то вам нужен немного другой запрос. Например:

SELECT firstname, lastname, ....
FROM profiles
WHERE id IN (
  SELECT id 
  FROM profiles
  WHERE firstName LIKE'%{firstname}%'
  UNION ALL
  SELECT id 
  FROM profiles
  WHERE lastName LIKE '%{lastName}%'
)