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

Ссылки

• MySQL, поменять столбцы местами
• MySQL для ленивых и практичных
• Удаление дубликатов в Mysql