Можно ли выполнить UPDATE + INSERT одним запросом?

Question

[entermix]

Составил запрос:

UPDATE `users` SET `balance` = balance + 11 WHERE `id` = '1' AND (INSERT INTO `payments` (`status_id`, `user_id`, `description`, `currency_id`, `amount`, `bonus_amount`, `sum`) VALUES ('2', '1', 'TEST', 643, 11, 22, 11)) LIMIT 1

При выполнении происходит ошибка:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO `payments` (`status_id`, `user_id`, `description`, `currency_id`, `amo' at line 1

По отдельности запросы работают. Что я делаю неправильно?

Answer 1

[Александр]

Нельзя так сделать.
Разбейте запрос на 2 части: INSERT отдельно, UPDATE отдельно; и заверните в транзакцию.
Баланс пользователя лучше высчитывать запросом из payments таблицы чем просто плюсовать.

Comments

[entermix]

А что, если если к примеру в payments история сохраняется всего месяц?

[Александр]

entermix: Значит что-то тут не так. Это же история, она должна хранится длительное время. Если вдруг система сломается или кто-то потрет значения баланса пользователей, как вы через месяц их восстановите?

[entermix]

Александр: тогда такой вопрос, есть модель "Пользователь" с методом "payment", который вызывается с контроллера, достаточно ли будет в самом методе обернуть 2 запроса UPDATE, INSERT в транзакцию и выполнить их, чтобы обеспечить целостность данных?

[Александр]

entermix: Да, так и надо сделать - 2 запроса в одном методе

[Артём Клименко]

entermix: раз у вас будут изменения в рамках транзакции также посмотрите в сторону SELECT FOR UPDATE

[entermix]

Артём Клименко: а зачем тогда SELECT FOR UPDATE, если используется транзакция? Открываем транзакцию, обновляем инфу о пользователе (или упускаем это, если считаем баланс прямо на сервере mysql), списываем средства, пришем в историю. Или я что то неправильно понимаю?

Answer 2

[Котик Антон]

Не понятно, зачем INSERT засовывать внутрь UPDATE? Выполняйте по отдельности, заворачивайте в транзакцию.
dev.mysql.com/doc/refman/5.7/en/commit.html

Comments

[entermix]

Чтобы выполнять 1 запрос вместо 3-х (UPDATE, SELECT, INSERT), не получится так? Или завернув запросы в транзакцию можно просто делать UPDATE + INSERT?

Answer 3

[Марат]

Если нужно 2 эти операции делать в связке, то можно закрепить это поведение в самой БД через триггер, который после выполнения UPDATE сам инициирует необходимый INSERT. Логику insert-а (в вопросе уже готовые значения) пропишите в теле триггера (для Update возможен доступ к значениям до выполнения команды через псевдозапись (или как правильно в Maria они называются) OLD, к новым значениям - через псевдозапись NEW.

CREATE TRIGGER `update_users` AFTER UPDATE ON `users`
FOR EACH ROW 
BEGIN
   INSERT INTO payments(status_id, user_id, description, currency_id, 
       amount, bonus_amount, sum) 
    values('2' ,new.id, 'test', 643, 
       old.balance, old.balance+new.balance, new.balance);
END;

тут свои + и -.
+) БД управляет сама поведением записей (на случай, если кто 'забудет' прописать вторую команду или оформить транзакцию, или кто вручную добавляет данные из PhpMySQLAdmin и т.п.) согласно заданной логике.
-) Нужно хорошо знать БД (где и что она хранит и т.п. ;) )
Вопрос производительности не имеет однозначного ответа, так как зависит от многих факторов (ресурсы и текущая нагрузка сервера, частота изменений ...)

Comments

[entermix]

Такой вопрос, допустим мы сделаем триггер на обновление баланса пользователя после покупки, будет ли это безопасно? Запрос + запросы в триггере будут выполнены в 1 транзакции?

[Марат]

entermix: Да. Надо также иметь в виду, что если в ходе выполнения триггера произошла ошибка, то откатится (rollback) команда, которая вызвала триггер.

[entermix]

Марат: Спасибо :)