Верх страницы
Обложка к записи Полезные приемы MySQL
Время для прочтения: 0 мин. 18 сек.

Полезные приемы 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 email
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);

Ссылки

Автор: Кобзарёв Михаил

Русский разработчик с 20-ти летним стажем. Работаю с PHP, ООП, JavaScript, Git, WordPress, Битрикс, Joomla, Drupal, Opencart, DLE, Laravel, Moonshine, SuiteCRM.

Оптимизирую сайты под Google Page Speed, настраиваю импорты для больших магазинов на WooCommerce + WP All Import. Пишу плагины на заказ. Все мои услуги.

Веду блог о разработке, дайджест в телеграмме и в ВК.

Вы всегда можете нанять меня.

Комментарии
Подписаться
Уведомить о
guest

4 комментариев
Межтекстовые Отзывы
Посмотреть все комментарии
Александр
Александр
12 лет назад

Спасибо, INSERT IGNORE очень удобная конструкция. Ещё одно подтверждение что маны читать полезно.

alex
alex
12 лет назад

Пример со новостью не совсем хорош — здесь связь 1к1, я думаю, имеет смысл поместить поле rating непосредственно в таблицу news.

Предыдущая запись
Следующая запись

Давайте дружить
в Телеграме

Авторский блог вашего покорного слуги в Telegram про web, программирование, алгоритмы, инструменты разработчика, WordPress, Joomla, Opencart, Laravel, Moonshine, фильмы и сериалы