Полезные приемы MySQL
В MySQL огромное количество полезных плюшек, о которых многие «программисты» даже не подозревают. Они сильно облегчают жизнь программисту и упрощают код.
Зачем писать серверные скрипты (PHP
, ASP
) для решения простых задач, если MySQL сама умеет их решать?
Я подготовил небольшой обзор наиболее употребляемых задач и способах их решений.
Обменять столбцы местами
Допустим мы имеем следующую таблицу table
в MySQL:
col1 | col2 | col3 | col4 | col5 |
---|---|---|---|---|
1 | 2 | 3 | 4 | 5 |
11 | 22 | 33 | 44 | 55 |
111 | 222 | 333 | 444 | 555 |
И хотим поменять местами столбцы col3
и col4
для получения такой таблицы:
col1 | col2 | col4 | col3 | col5 |
---|---|---|---|---|
1 | 2 | 4 | 3 | 5 |
11 | 22 | 44 | 33 | 55 |
111 | 222 | 444 | 333 | 555 |
Для решения этой задачи достаточно выполнить несложный запрос:
ALTER TABLE `table` MODIFY COLUMN `col4` integer AFTER `col2`;
Вставить запись, если ее еще нет
Допустим, у вас есть таблица subscribers
, хранящая адреса почтовых ящиков ваших подписчиков.
Обычно, при добавлении нового подписчика вы проверяете, есть ли уже данный email в таблице или нет. Если его нет — добавляете, есть — ничего не делаете.
Структура таблицы subscribers
:
id | |
---|---|
1 | foo@bar.com |
2 | bar@foo.com |
На это вы тратите 2 запроса к MySQL и пишите несколько строк на РНР. Но можно поступить иначе.
Делаете столбец email
таблицы subscribers
уникальным. Ведь не существует же одинаковых адресов.
ALTER TABLE `subscribers` ADD UNIQUE(`email`);
А теперь при добавлении нового подписчика выполняем всего один запрос:
INSERT IGNORE INTO `subscribers` SET `email` = '$email';
Обновление счетчиков
Предположим, что у вас есть таблица news
для хранения новостей сайта и таблица news_rating
— для хранения рейтинга каждой новости.
При просмотре конкретной новости нам необходимо обновить ее рейтинг: при первом заходе создать новую запись в таблице news_rating
, установив начальное значение счетчика равное единице; при повторном заходе — инкрементировать этот счетчик, увеличивая его значение на единицу.
Структура таблицы news
:
id_new | name |
---|---|
1 | Заголовок новости |
2 | Заголовок новости |
Структура таблицы news_rating
:
id_new | rating |
---|---|
1 | 123 |
2 | 753 |
На это вы тратите 2 запроса к MySQL и пишите несколько строк на РНР. Но можно поступить иначе.
Делаем столбец id_new
уникальным. У вас ведь не бывает разных новостей с одинаковым ID?
ALTER TABLE `news_rating` ADD UNIQUE(`id_new`);
А теперь при обновлении счетчика выполняем всего один запрос:
INSERT INTO `news_rating` SET `id_new` = $id_new, `rating` = $rating ON DUPLICATE KEY UPDATE `rating` = `rating` + 1
Сортировка по значению
Допустим, у нас есть таблица с заказами orders
, в которой статус заказа представлен полем типа enum
:
id_order | status |
---|---|
1 | Новый, Комплектуется, Отправлен |
2 | Новый, Комплектуется, Отправлен |
Задаем порядок для сортировки:
SELECT * FROM orders ORDER BY FIELD ( `status`, 'Новый', 'Комплектуется', 'Отправлен' )
Выбор записей за последние N
дней
Обычно, чтобы выбрать данные за последнюю неделю по индексированному полю используют следующий синтаксис
AND DATE(`registered`) >= CURRENT_DATE() - interval 7 DAY
или
AND TO_DAYS(CURRENT_DATE) — TO_DAYS(`registered`) <= 7
В обоих случаях индекс использоваться не будет, так как происходят преобразования над индексированным полем.
Правильнее будет сделать так
`registered` >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
Поиск и удаление дубликатов
Ищем дубликаты по колонке login
:
SELECT login, COUNT(login) AS cnt FROM sys_users GROUP BY login HAVING ( COUNT(login) > 1 )
Удаляем дубликаты по колонке login
DELETE t1 FROM sys_users t1, sys_users t2 WHERE t1.login=t2.login AND t1.ID > t2.ID
Либо более красивый способ:
ALTER IGNORE TABLE sys_users ADD UNIQUE INDEX(login);
Спасибо, INSERT IGNORE очень удобная конструкция. Ещё одно подтверждение что маны читать полезно.
Маны «курить» очень полезно, чтобы не изобретать велосипед
Пример со новостью не совсем хорош — здесь связь 1к1, я думаю, имеет смысл поместить поле rating непосредственно в таблицу news.
Это всего лишь пример…