Время для прочтения: 2 мин. 42 сек.

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

Ссылки

Для вставки кода используйте HTML-теги
<pre><code class="php">ваш код</code></pre>

  • alex

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

  • Александр

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