Несколько интересных приемов и особенностей работы с MySQL

Я думаю, что в процессе изучения той или иной СУБД каждый из вас не раз изобретал велосипеды для решения своих задач, не зная о существовании той или иной функции или приема, которые бы могли в разы ускорить выполнение запросов и уменьшить объем кода. В данной статье я хочу поделиться с вами своим опытом работы с очень «добрым» и «отзывчивым» MySQL, часто позволяющему программисту делать вещи, которые другие СУБД переварить бы не смогли. Материал будет полезен скорее тем, кто только решил углубиться в чудесный мир запросов, но возможно и опытные программисты найдут тут что-то интересное.

Удаление дубликатов

Очень часто на различных специализированных ресурсах мне встречались вопросы о том, как быстрее и оптимальнее избавиться от дублирующихся записей в таблице. Сразу же в голову приходит то, что нужно создать еще одну таблицу, идентичную данной, создать в ней уникальный ключ и скопировать в нее данные из исходной таблицы, скажем, с помощью INSERT IGNORE. Но существует и более простой способ. Достаточно просто создать в таблице уникальный ключ с помощью такого вот запроса:

ALTER IGNORE TABLE table1 ADD UNIQUE (field1, field2);

После добавления ключа все дубликаты удалятся автоматически.

Преобразование строки в число

Допустим перед вами встала задача поиска в таблице адресов address дома с определенным номером. Причем номер дома хранится в текстовом поле num, содержащим значения типа '1', '1а', '1/б', '2ы', '3йцукен' и т.п. И мы хотим выбрать дома с номером, состоящим из 1 и еще каких-то символов. Думаю многие сразу кинутся искать решение с использованием LIKE или REGEXP. Но проще будет использовать следующую фичу MySQL:

SELECT *
  FROM address
  WHERE num + 0 = 1;

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

SELECT '1qwe3s' + '2regt3g';

И в результате мы получим ответ: 3.

Использование переменных в запросах

Тут я сразу приведу пример решения задачи, в которой переменная облегчит нам жизнь.
Имеем следующую таблицу table1:

Нужно вывести все эти поля и добавить к ним еще 2, onStart и total.
total = summ — onStart.
onStart равен значению total из предыдущей записи, для первой записи onStart = 0.
Т.е. в итоге мы должны получить такой вот результат:

Использую переменную, мы сможем при решении данной задачи избавиться от лишних JOIN'ов и подзапросов:

SELECT t1.id, t1.summ, @i AS onStart, @i := t1.summ - @i AS total
  FROM table1 t1
  JOIN (SELECT @i := 0) var;

Подсчет количества различных записей в таблице

Еще одна часто встречающаяся задача. И тут я тоже сразу приведу пример.
Дана таблица table1 (id, f1, f2). Нужно написать запрос, который бы вернул нам следующий результат:

Конечно можно получить результат вот так:

SELECT COUNT(1),
      (SELECT COUNT(1) FROM table1 WHERE f1 = 1),
      (SELECT SUM(f2) WHERE f1 = 2)
  FROM table1;

Но очевидно, что это далеко не оптимальное решение. Придется для каждой записи выполнять еще два дополнительных подзапроса. И мы сделаем по-другому:

SELECT COUNT(1),
       SUM(f1 = 1),
       SUM(IF(f1 = 2, f2, 0))
  FROM table1;

Теперь другое дело. Всё, что нам нужно, мы посчитали за один проход по таблице.

Column 'id' in group statement is ambiguous

В этой части статьи я хочу обратить ваше внимание на одну интересную особенность MySQL.
Имеем такой запрос:

SELECT t1.id, t2.id
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id_t1
  GROUP BY id;

Видно, что в блоке GROUP BY мы забыли указать алиас у поля id, и соответственно при попытке выполнить запрос получили ошибку «Column 'id' in group statement is ambiguous». Казалось бы всё верно. Теперь изменим этот запрос:

SELECT t1.id, t2.f1
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id_t1
  GROUP BY id;

Мы убрали из списка выводимых полей t2.id и, о чудо, запрос отработал, данные были сгруппированы по t1.id. Другие СУБД, такие как, например, MS SQL или PostgreSQL и во втором случае выдали бы ошибку, но для MySQL второй запрос полностью корректен.
Так что я рекомендую вам быть более внимательными и всегда использовать алиасы перед полями, иначе потом при небольшом изменении запроса можно нарваться на ошибку.

Поиск данных за последнюю дату

И напоследок хочу привести еще один пример решения одной типичной не сложной часто встречающейся задачи. Почему-то у многих она часто вызывает затруднения.
Дана таблица платежей payments (id INT, uid INT, pay_date DATE, amount DECIMAL(15, 2)).
id – первичный ключ
uid – идентификатор юзера
pay_date – дата платежа
amount – сумма платежа
Нужно написать запрос, который бы вывел для каждого юзера дату и сумму последнего платежа.
Я предлагаю вам следующее стандартное решение:

SELECT p.uid, p.amount
  FROM payments p
  JOIN
    (SELECT uid, MAX(pay_date) AS max_dt
       FROM payments
       GROUP BY uid) sel ON p.uid = sel.uid AND p.pay_date = sel.max_dt;

Автор: AlexeyVD