Отправляет email-рассылки с помощью сервиса Sendsay

Как стать программистом и избежать детских ошибок

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "Работа в сети Интернет" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Декабрь 2009   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Автор
Павел Корягин

Статистика

730 подписчиков
-1 за неделю
←   Все выпуски   →

Как стать программистом и избежать детских ошибок Больше SQL


Сегодня я исправил досадное упущение: в моём ЖЖ, где Вы можете комментировать выпуски рассылки, делать это дозволялось лишь пользователям ЖЖ. Теперь это можно всем посетителям.

Кроме того, предыдущий опрос показал, что почти вся аудитория блога и рассылки скорее всего не имеет учётной записи в ЖЖ. Однако мне хочется не только вещать в одну сторону, но и поддерживать с Вами, дорогой читатель, обратную связь. Поэтому сегодня я предложу Вам микрозадачку на понимание одного из базовых аспектов программирования.

Этой статьёй я завершаю цикл про работу с БД, начатый здесь, здесь, здесь и здесь. К теме ORM мы вернёмся позже. Следующий выпуск будет про личностные качества и их влияние на отношения с клиентом, с пользователем, с начальником и с коллегами.

Кстати, откуда я беру ошибки, которые собрал в своём курсе и которые описываю здесь?

Встречаю я их в реальных программах, с которыми я сталкиваюсь по роду деятельности. Реже чужие ошибки появляются в блогах на Хабре, иногда в обличительных статьях, а иногда и в просвещающих.

То же самое касается и психологии: я вижу, как работают те, кому хочется платить, лишь бы сотрудничество было долгим, и те, кого хочется прогнать с позором (или вообще не нанимать, если проблема видна на подходе). К сожалению, первых мало, поэтому я и создал Школу, одной из задач которой является воспитание ответственности и понимания смежных фронтов работ.

Почему я смело говорю, что курс собран из ошибок? Потому что прикладное знание — это умение различать вредное от полезного. Если Вы знаете правильный рецепт и только его, то это сослужит Вам плохую службу там, где он неприменим. Поэтому я предлагаю одновременно плохое и хорошее, чтобы была видна разница. Это также объясняет и то, что всю теорию я даю после примеров, в которых она нужна, а не наоборот.

Как заявлено на первой странице Школы: «Это — курс от практика».

Также можно спросить, почему я не дал сразу ORM, вместо разбирания трюков с SQL-запросами. Да потому что я знаю, что большинство всё равно будут писать дедовскими методами (в частности, дорабатывая готовый код) — пусть хотя бы это делают хорошо.

В качестве предисловия к сегодняшнему выпуску хочу напомнить одно из правил успешного программиста: Видим незнакомое слово — сразу в Гугль или Вики.

Что такое SQL?

Судя по большинству программ, SQL представляется среднему разработчику такими операторами:

  • SELECT * [или список полей] FROM table WHERE ...
  • INSERT (в форме VALUES)
  • UPDATE table SET ... WHERE ...
  • DELETE FROM table WHERE ...

Иногда встречаются также:

  • SELECT * [или список полей] FROM table1 t1, table2 t2 WHERE ...
  • INSERT (в форме SET)

Но это не весь SQL. Он гораздо богаче и удобнее. Рассмотрим несколько задач.

Количество записей

Напоминаю, функция db_query является обёрткой mysql_query или её аналога. Дополнительные функции, вроде db_query_value, описаны ранее, хотя их смысл будет очевиден из примеров использования.

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

$q = db_query("SELECT * FROM user WHERE gender = 'F'");
$count = 0;
while ($row = db_fetch_array($q))
  $count++;

К счастью, такое встречается крайне редко, но упомянуть стоило. А вот очень популярный, но тоже неправильный вариант:

$q = db_query("SELECT * FROM user WHERE gender = 'F'");
$count = db_num_rows($q);

В этом случае СУБД построит набор строк, чтобы отдать его нам. А нам из него нужно только количество. Плюс мы опять получаем лишний дескриптор в поле зрения.

Так вот, для таких задач SQL содержит функции агрегирования. Одна из которых — COUNT — возвращает количество строк.

В данном случае код получился бы таким:

$count = db_query_value(
  "SELECT COUNT(*) FROM user WHERE gender = 'F'");

Получилось семантично: мы хотим количество, мы и просим количество (select count for me, please).

Кстати, тут самое время заметить, что синтаксис SQL задуман максимально близким к естественному английскому. Предложения вполне читаются буквально. Некоторые директивы состоят из двух слов даже без знака подчёркивания между ними (order by, delete from, insert into).

А поскольку предложения являются командами, обратите внимание: начитаются они всегда с глагола.

Подсчёты на лету

В SELECT мы можем использовать выражения вместо полей. И даже константы:

SELECT id, 'Mr. Hacker' AS name, registered + INTERVAL 5 DAY
FROM users WHERE id = 5938

Правда, оператор INTERVAL из этого примера — особенность MySQL, зато всё остальное будет работать везде.

Например, мы можем поинтересоваться у СУБД на предмет случайного числа:

if (rand(0, 99) ==
  db_query_value("SELECT FLOOR(RAND() * 100)"))
  echo 'Бинго!';

Тест

Если же нам нужно узнать существует ли запись в таблице, и больше ничего от этой записи нам не нужно, то:

if (db_query_value("SELECT 1 FROM sessions WHERE user_id = ?",
  $_GET['user_id']))
  echo 'Да, он сейчас онлайн.';

Кстати, оператор «SELECT 1» оказывается нужен не так уж и редко. (P.S. «1» здесь — это обычная константа, можно использовать другую, если потребуется)

Сортировка

Далее, если Вам нужно отсортировать выборку, не делайте этого в PHP. Сэкономите и время и оперативную память (и себе, и машине). Разберитесь с тем, как работает «ORDER BY».

SELECT * FROM items ORDER BY created DESC
SELECT * FROM users ORDER BY lastname, firstname

Это гораздо лучше, чем извлекать всю выборку и сортировать её после этого.

Кроме того, разберитесь и с «LIMIT». Это полезная директива, помогающая организовать постраничную разбивку и сократить время формирования первой страницы. Хотя, к сожалению, MS SQL и Oracle поддерживают совершенно другие синтаксисы для этой задачи, нежели MySQL и PostgreSQL.

А вот так, например, в MySQL можно выбрать следующую запись за той, чей идентификатор был сохранён ранее.

SELECT * FROM articles ORDER BY id <= $my_stored_id, id LIMIT 1

Перемешивание

Образец:

SELECT * FROM sweets ORDER BY RAND()

Тем более не стоит делать этого на PHP или Perl: пусть перемешивает сама СУБД.

Частный случай, выбор нескольких случайных записей:

SELECT * FROM sweets ORDER BY RAND() LIMIT $skoko_nado

Клонирование

Если Вам требуется сделать копию некоторых строк в другую или в ту же самую таблицу, то незачем извлекать их из базы данных. Вам поможет оператор «INSERT... SELECT».

-- Клонировать все вещи
INSERT things SELECT * FROM things

-- Клонируем дам, исключая из списка полей id'ы,
-- поскольку они генерируются автоматически,
-- и новым указываем регистрацию сегодняшним числом.
-- Пароль они тоже не унаследуют.
INSERT users(name, email, password, gender, registered)
SELECT name, email, MD5('123'), gender, NOW()
FROM users
WHERE gender = 'F'

INSERT ... SET

Часто вижу в универсальных диалоговых формах (добавить + изменить в одном флаконе) два разных алгоритма вклеивания данных в запрос. Один для INSERT ... VALUES в виде двух строк: перечень полей, затем перечень значений; другой для UPDATE, когда поля со значениями идут парами в одной строке.

Если Ваша СУБД — MySQL (и только в этом случае), то это не обязательно. Используйте синтаксис SET для обеих команд.

А для него в свою очередь используйте плейсхолдер «?%».

Для других СУБД проблема может решаться отдельными функциями на уровне Вашего фреймворка. Это будет более универсальное решение, но оно за пределами сегодняшней темы.

Перечни

Если Вы работаете с MySQL, то обратите внимание на тип ENUM.

Вместо такого:

define('MY_INVOICE_STATUS_ACTIVE', 0);
define('MY_INVOICE_STATUS_PAID', 1);
define('MY_INVOICE_STATUS_EXPIRED', 2);
define('MY_INVOICE_STATUS_DEFERRED', 3);

он позволяет хранить человеко-читаемые данные, но место занимает, как целое число. Пользоваться можно, как строками:

UPDATE invoces SET status = 'paid' WHERE invoice_id = 35541

Извлечение зависимых значений

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

$q = db_query("SELECT id, name, group_id FROM users");
while ($row = db_fetch_assoc($q))
{
  $row['group_title'] = db_query_value(
    "SELECT title FROM groups WHERE id = ?", $row['group_id']);
  ...
}

Разумеется, без db_query_value выглядело бы ещё страшнее. Ещё это медленно, несемантично (то есть затрудняет чтение) и может превысить лимит запросов на хостинге (я ранее уже лечил CMS от этой напасти).

Лучше освойте синтаксис JOIN, и пользуйтесь им разумно.

-- Извлечь пользователей с названиями групп, или NULL
-- если пользователь не входит в группу.
SELECT u.id, u.name, g.title AS group_title
FROM users u
  LEFT OUTER JOIN groups g ON g.id = u.group_id

Обратите внимание, что ON требуется для связки таблиц. При этом дополнительные условия в случае с OUTER JOIN будут работать по-разному:

-- Извлечь джентльменов с названиями групп, или NULL
-- если пользователь не входит в группу.
-- Дополнительным условием будет то,
-- что нас интересуют только группы «синего» сектора

-- а)
SELECT u.id, u.name, g.title AS group_title
FROM users u
  LEFT OUTER JOIN groups g ON
    g.id = u.group_id AND g.is_blue
WHERE u.gender = 'M'

-- б)
SELECT u.id, u.name, g.title AS group_title
FROM users u
  LEFT OUTER JOIN groups g ON g.id = u.group_id
WHERE u.gender = 'M' AND g.is_blue

В случае «а» из базы будут выбраны все джентльмены. При этом группа будет указана лишь у тех, кто работает в синем секторе.

В случае «б» из базы будут выбраны только те джентльмены, кто работает в синем секторе. И для всех их будут указаны группы.

Обратной стороной медали является то, что большое нагромождение JOIN’ов вопреки ожиданиям повредит производительности. Однако, оптимизация как таковая имеет свою собственную философию, и она достойна отдельной статьи. Пока смело пользуйтесь JOIN’ами.

Кроме того, есть ещё подзапросы, но я о них благородно умолчу, показав лишь один интересный пример (два запроса в одном, не выходя в PHP из MySQL):

db_query("
  INSERT INTO tree
  SET ?%, created = NOW(), itemorder =
    (SELECT COALESCE(MAX(sub.itemorder), 0) + 1
     FROM tree sub
     WHERE sub.parent = ?)",
  $fields, $fields['parent_id']);

И, разумеется, не следует вытаскивать разные ячейки одной записи в несколько запросов.

Атомарные операции

А вот хитрые грабли, на которые рано или поздно встаёт каждый.

Если Вы добавили на сайт голосование и сделали это «самым очевидным образом»:

$count = db_query_value("
  SELECT vote_count
  FROM articles
  WHERE article_id = ?",
  $article_id);

$count++;

db_query("
  UPDATE articles
  SET vote_count = ?
  WHERE article_id = ?",
  $count, $article_id);

то, при высокой интенсивности голосов, Вы заметите, что часть из них теряются. Или не заметите.

Происходит это потому, что по законам статистики часть голосов придёт на сервер одновременно. Обрабатываться эти запросы тоже будут одновременно, и первый из приведённых операторов также одновременно сработает в разных процессах, вернув одно и то же значение. Каждый процесс, честно увеличит это значение на единицу, учтя лишь себя, и запишет в базу данных. Таким образом, результатом обработки нескольких запросов станет один голос.

Этого не произойдёт, если сделать инкремент в один запрос:

db_query("
  UPDATE articles
  SET vote_count = vote_count + 1
  WHERE article_id = ?",
  $article_id);

СУБД следит за тем, чтобы данные не повреждались в течение запроса. Такие запросы называются атомарными (т. е. буквально: неделимыми).

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

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

Читаемость

И самое-самое главное: пишите не только для ЭВМ, но и для программиста. SQL-запрос не является некой строкой; данными, зашитыми в программу. Это — часть программы. Такая же, как и тот язык, что подсвечивается в вашем редакторе.

Скриптовые языки в основном поддерживают многострочные строковые константы. Это хорошо. Поэтому пишите длинные запросы, размечая и перенося их логические части и помня о правом крае экрана.

Например, так:

$q = db_query("
  SELECT
    o.id AS order_id, o.count, o.total, o.date,
    u.id AS user_id, u.lastname, u.firstname, u.age,
    u.gender, u.photo,
    g.title AS group_title
  FROM orders o
    INNER JOIN users u ON o.user_id = u.id
    LEFT OUTER JOIN groups g ON u.group_id = g.id
  WHERE o.product_id = ?
  ORDER BY o.date DESC, o.id DESC
  LIMIT ?, ?
  ", $product_id, $page_start, $page_size);

Даже мой генератор кода умеет делать красивый SQL. Не будьте глупее машины.

Резюме

Помимо перечисленных рецептов я советую Вам разобраться с:

  • Полным списком скалярных функций Вашей СУБД (вроде NOW, MD5, CONCAT, COALESCE и т. п.).
  • Полным списком функций агрегирования (в частности, Вам нужны COUNT, MIN и MAX, SUM).
  • Предыдущий пункт потребует также знакомства с «GROUP BY».
  • Разобраться с оператором IN.
  • И, возможно, разобраться с подзапросами.

Этого достаточно.

Обещанная задача

Задача на понимание принципов программирования.

Что лишнее в этом коде на JavaScript и почему?

if (a.checked == true)
  x = b.value * 1 + c.value * 1;
else
  x = 0;

Ответы жду в комментариях — теперь Вам для этого не нужен аккаунт в ЖЖ. Комментарии открыты для всех.

Этот выпуск Вы можете прокомментировать в Живом Журнале.

Задать вопрос

Вы можете задать мне любой вопрос. Все полученные мною вопросы могут быть опубликованы в рассылке. Если Вы желаете скрыть свои личные данные из вопроса — укажите это в тексте письма, поскольку в дальнейшем, письма будут публиковаться с полными подписями (без емейлов, разумеется).
В избранное