This is a scrolling ticker line using pure CSS and HTML! This ticker system comes included as part of all Nulumia themes.
You can customize these lines in your admin panel, works purely off Style Properties!
You can even add links, and other HTML markup! Responsive and customizable.

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

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

Очень часто на различных специализированных ресурсах мне встречались вопросы о том, как быстрее и оптимальнее избавиться от дублирующихся записей в таблице. Сразу же в голову приходит то, что нужно создать еще одну таблицу, идентичную данной, создать в ней уникальный ключ и скопировать в нее данные из исходной таблицы, скажем, с помощью INSERT IGNORE. Но существует и более простой способ. Достаточно просто создать в таблице уникальный ключ с помощью такого вот запроса:
[/SIZE]ALTER IGNORE TABLE table1 ADD UNIQUE (field1, field2);[SIZE=5]

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


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

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

Код:
SELECT *
  FROM address
  WHERE num + 0 = 1;
Встретив арифметическую операцию, MySQL автоматически приводит все аргументы к числовому типу. В случае со строками будут просто отсечены все символы, начиная с первого не числового.
Вот такой вот запрос тоже спокойно выполнится без ошибок:


SELECT '1qwe3s' + '2regt3g';

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


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

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


idsum
135
225
310
455
512

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


idsumonStarttotal
135035
22535-10
310-1020
4552035
51235-23

Использую переменную, мы сможем при решении данной задачи избавиться от лишних 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). Нужно написать запрос, который бы вернул нам следующий результат:


общее количество записейколичество записей с f1 = 1сумму значений f2 для f1 = 2

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


Код:
SELECT COUNT(1),
      (SELECT COUNT(1) FROM table1 WHERE f1 = 1),
      (SELECT SUM(f2) FROM table1  WHERE f1 = 2)
  FROM table1;
Но очевидно, что это далеко не оптимальное решение. Придется для каждой записи выполнять еще два дополнительных подзапроса. И мы сделаем по-другому:
SELECT COUNT(1),
SUM(f1 = 1),
SUM(IF(f1 = 2, f2, 0))
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 DATETIME, amount DECIMAL(15, 2)).
id – первичный ключ
uid – идентификатор юзера
pay_date – дата платежа
amount – сумма платежа
Нужно написать запрос, который бы вывел для каждого юзера дату и сумму последнего платежа.
UPD. Считаем, что юзер не может провести больше одного платежа за секунду. (Без этого условия постановка задачи некорректна). Тип pay_date изменен с DATE на DATETIME.
Я предлагаю вам следующее стандартное решение:

Код:
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;