2 мар. 2011 г.

Вставка или обновление записи в MySQL


MySQL
Иногда бывают такие случаи, когда нужно добавить данные о каком либо объекте в таблицу базы данных, но заранее не известно есть какие-либо сведения об объекте уже есть в таблице.

    Объясню попроще. Пусть у нас есть две таблицы: users – таблица с данными пользователей с полями id, login, password, где id – первичный ключ; есть таблица users_rating с рейтингом пользователей, поле – user_id – внешний ключ id в users, поле rating – сам рейтинг.

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



Способ номер один. Не самый лучший.

Делаем выборку записей из таблицы с нужным user_id

Если выборка пустая – вставляем данные, иначе – обновляем

  1. SELECT rating FROM users_rating WHERE user_id=N;
  2. INSERT INTO users_rating VALUES(N,5);
  3. или
  4. UPDATE users_rating SET rating=rating+5 WHERE user_id=N;


Чем плох этот способ? Тем, что в нем используются два запроса к базе данных. Может быть и совсем легких, но все же их два.
Способ номер два. Уже лучше.

Заключается в использовании REPLACE. Синтаксис запроса аналогичен запросу на обновление или на вставку. При выполнении запроса, движок сначала попытается выполнить вставку, но, если запись с указанным значением поля уже существует, старая запись будет удалена и создастся новая. В этом и основной минус такой конструкции – в том что для обновляемой записи обновляются и автоинкрементные поля. В нашей таблице таких нет, поэтому способ вполне подходит.


  1. REPLACE users_rating SET rating=rating+5 WHERE user_id=N;


Способ номер три. Мой выбор :) .

Заключается в использовании конструкции INSERT … ON DUPLICATE KEY UPDATE

Движок будет пытаться выполнить вставку записи, но если в полях с уникальным индексом бдует найдено повторение, первая встретившаяся запись будет обновлена. С осторожностью стоит использовать эту конструкцию в таблицах, которых уникальными являются несколько полей. Обновится только первое из совпадений.


  1. INSERT INTO users_rating (user_id, rating) VALUES(N, 5)
  2. ON DUPLICATE KEY UPDATE rating = VALUES(rating);


Вот такими методами можно вставить, либо обновить запись, если она уже существует.