08 февраля 2006

Хитрый SQL

Я заметил, что частенько работа с БД состоит в такой последовательности шагов:
1. Проверить наличие определённых данных в PRIMARY KEY-поле таблицы (или просто уникальном) через SELECT.
2. Если есть такая запись, то UPDATE этой строки.
3. Если нет такой записи, то INSERT.

Как ни крути, получается как минимум 2 запроса и условие на стороне сервера приложений. А мне бы хотелось перенести часть работы на сервер БД.

Сначала я начал копать оператор IF, но он, насколько я разобрался, работает внутри SELECT, и так не получится, разве что писать хранимую процедуру, что но до недавнего времени это было невозможно в MySQL.

Поэтому усиленное копание мануала привело к такой замечательной структуре, о которой, вероятно, мало кто слышал. Она построена на базе связки INSERT INTO - SELECT FROM, только оказалось, что в неё можно встроить и UPDATE. Выглядит так:

INSERT INTO table1 (name, role)
SELECT FROM table2 name, role
ON DUPLICATE KEY UPDATE name='Vasya', role=DEFAULT


Небольшой минус в том, что в первых двух строках задаётся только список полей , а в третьей - списком полей с их значениями, т.е. его нужно заранее нагенерить. Но в целом это помогло мне решить и упростить целый ряд задач.

8 комментов:

Dmitry Kalabin комментирует...

и что получается? если у тебя например name - это PK, SELECT из table2 выбрал несколько записей (например: "Petya", "Kolyan") и при этом оказалось что и "Petya" и "Kolyan" уже есть в table1! UPDATE пытается сделать Васей и Петю и Коляна(смешной пример получился :) ), но два Васи быть не может, потому что это PK! Вот мы и запутались совсем... :(

UPDATE... FROM можно в таком стейтменте писать? Потому как только в этом случае ИМХО можно реальную пользу извлечь.

A4 комментирует...

name у меня точно не PK, я обычно ID (int) использую для этого. А вот то, что уникальным name может быть - это да, вполне.

UPDATE... FROM тебе не понадобится, потому что есть SELECT... FROM: обзови сурс-таблицу алиасом и используй его в UPDATE свободно.

Я, например, селект делал из временной таблицы, где нужные данные были сформированы. При этом при UPDATE-модификации данных в главной таблице нужно было перед изменением поля price скопировать существующее значение в поле old_price, и только потом затирать новым значением. В таком случае кастрированный запрос примет вид:

INSERT INTO product_offers (`id_product`, `price`)
SELECT `id_product`, `new_price`
FROM temp_table t
WHERE `id_product` IS NOT NULL
ON DUPLICATE KEY UPDATE `old_price`=`price`, `price`=t.`new_price`

kpumuk комментирует...

А почему бы не использовать просто REPLACE INTO?

A4 комментирует...

REPLACE INTO я не использовал потому что это по сути [DELETE + ] INSERT, верно? И как тогда апдейтить - делать перед этим дополнительный SELECT? Нее.

Что меня раздражает в обоих подходах: "The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated", т.е. пойди реально узнай, сколько строк код затронул...

kpumuk комментирует...

Насколько я помню, то DELETE не происходит. Просто обновляются поля, которые указаны. Остальные остаются нетронутыми

A4 комментирует...

Могу предложить только цитату из мануала: REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted

kpumuk комментирует...

Хехе. Сорри, был не прав, каюсь :-)

A4 комментирует...

Это ничего, зато у тебя блог классный :]

Отправить комментарий