На программистских форумах в разделах MySQL регулярно появляются темы с просьбами помочь найти ошибки в SQL-запросах. Ошибки, как правило, оказываются тривиальными и очень легко обнаруживаются без привлечения консилиума форумных гуру. Для новичков и просто ленивых я попробую описать приемы, которыми пользуюсь для отладки запросов сама.
Простые запросы
Итак, написали мы простенький запрос, отдаленно похожий на этот:
$result = mysql_query("SELECT id FROM picture WHERE album_id='{$_GET['album']} AND display='yes''");
while ($res = mysql_fetch_array($result, MYSQL_ASSOC)){
// выводим результаты на экран
}
Запускаем страничку и вместо предполагаемого результата видим:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
Первое, что нужно сделать - вывести текст запроса на экран. На этом этапе я обычно и нахожу большую часть ошибок. Итак, печатаем запрос. Например, так:
$sql = "SELECT id FROM picture WHERE album_id='{$_GET['album']} AND display='yes''";
print "$sql";
$result = mysql_query($sql);
И получаем:
SELECT id FROM picture WHERE album_id=' AND display='yes''
Картина проясняется:
- Переменная $_GET['album'] не инициализирована. Причины могут быть самые разные, чаще всего это опечатка в имени переменной. В данном случае, например, я "забыла" передать скрипту параметр album. На самом деле таких ошибок легко избежать, если включить режим ошибок E~ALL (функцией error_reporting(E_ALL) или в ini-файле, если к нему есть доступ).
- Банальная опечатка: продолжение условия WHERE попало в кавычки значения поля name.
Да, крайне удобно отлаживать запросы в редакторах с подсветкой синтаксиса SQL или непосредственно в phpMyAdmin - сразу становятся видны все опечатки в написании ключевых слов и путаница в кавычках. А еще php-админка сразу выводит код и текст ошибки.
Исправили, запускаем снова:
SELECT id FROM album WHERE album_id='2' AND display='yes'
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
Теперь запрос выглядит корректно, но почему-то все еще не работает. На следующем шаге выводим на экран номер и текст сообщения об ошибке MySQL:
$sql = "SELECT id FROM picture WHERE album_id='{$_GET['album']} ' AND display='yes'";
$result = mysql_query($sql);
print mysql_errno()." - ".mysql_error();
И получаем:
1054 - Unknown column 'album_id' in 'where clause'
Если текст ошибки Вам ни о чем не говорит, обратитесь к русскоязычным FAQ с описанием ошибок MySQL. Здесь очевидно, что мускул не знает столбца с именем album_id.
Наиболее вероятная причина - опечатка в имени столбца. Часто опечатку можно не заметить в больших запросах или когда имена составлены из аббревиатур и сокращений. Менее вероятно - вы переименовали столбец и забыли. Исправляем имя и получаем, наконец, искомый результат.
Пример, конечно, сильно притянут за уши, и трудно найти такую концентрацию глупых ошибок в одном простом SQL-запросе. Но мне не хватило бы фантазии на несколько разных примеров.
Запросы к нескольким таблицам
Теперь рассмотрим более сложные запросы к нескольким таблицам с группировкой результата. Методы отладки такие же, поэтому я сразу покажу на примере корректно работающего запроса, где чаще всего возникают ошибки. Итак:
SELECT COUNT(a.id) AS app_count,
SUM(a.square) AS sum_square,
h.id AS h_id,
s.id AS s_id,
s_name AS s_name,
h.number AS h_num,
h.literal AS h_lit,
h.sum_square AS full_square,
h.sum_apps AS full_apps
FROM street AS s,
house AS h,
appartment AS a
WHERE h.street = 10
AND h.id = a.house
AND a.private = 'no'
GROUP BY h.id
ORDER BY h.number ASC
SELECT house.id, house.number, house.prefix,
house.sum_square AS full_square,
house.sum_apps AS full_apps
FROM house LEFT JOIN appartment
ON house.id = appartment.house
WHERE street = 8
AND appartment.id IS NULL
ORDER BY house.number ASC
В запросах, которые должны вернуть много полей, таблицам и полям лучше назначать "псевдонимы". Таблицам - для более краткой записи, полям - чтобы не было конфликтов имен и не запутаться потом при работе с результатом запроса.
Пройдемся по потенциально "узким" местам:
- Очень легко запутаться в длинных перечнях полей и условий с запятыми и логическими условиями, особенно при редактировании. Поэтому лучше оформлять длинные запросы в отформатированном виде.
- Если нужно сравнить поле с NULL-значением, используется не знак равенства, а ключевое слово IS NULL или IS NOT NULL - в зависимости от того, на что проверяем. Для проверки на принадлежность некоторому множеству используется конструкция "field IN (v1, v2,…)" ("field NOT IN (v1, v2,…)"). Для соответствия образцу используются ключевые слова LIKE (NOT LIKE), или RLIKE (NOT RLIKE) - для регулярного выражения.
- Если не назначить псевдонимы полям h.id и s.id, ошибки не будет. Просто в результирующем массиве два поля будут иметь одинаковые ключи. Это не проблема, если предполагается обращаться к массиву по индексам. Однако при добавлении или исключении из запроса некоторых полей, придется отслеживать и менять индексы по коду обрабатывающей функции.
- Если обратимся к полю id без префикса с именем таблицы h, получим сообщение об ошибке
#1052 - Column: 'id' in where clause is ambiguous
Необходимо явно указать, id из какой таблицы имеется ввиду. Попытка обратиться к полю по псевдониму также приведет к ошибке. - Важно не перепутать порядок следования дополнительных условий, таких как GROUP BY, HAVING (которое идет только в связке с GROUP BY сразу после него), ORDER и LIMIT. Если поменять местами блоки GROUP BY и ORDER BY, сообщение об ошибке будет не самым "говорящим", и не всегда можно быстро сообразить, что именно ему не нравится, служебное слово или его положение в запросе:
#1064 - You have an error in your SQL syntax near 'GROUP BY h.id
- Работать с объединениями без должного опыта нужно очень осторожно. Неправильное использование объединений может привести к неожиданным результатам и труднообнаружимым логическим ошибкам.
Если вы не часто работаете с базами данных, для составления сложных запросов полезно иметь под рукой справочник или книгу по SQL. Впрочем, иметь справочный материал нужно в любом случае.
Сложные запросы
Если запрос включает очень сложные условия, объединения или подзапросы, синтаксически корректен, но не возвращает строк, я обычно начинаю составлять запрос в phpMyAdmin. Сначала без блока условий, потом добавляю условия и группы условий одно за другим, на каждом этапе запускаю запрос на выполнение и анализирую результаты. Никаких универсальных рецептов здесь нет, только нудная работа.
