Как легко вставить или обновить или удалить запись сразу

Я создал таблицу

CREATE TABLE `pledge` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `user` VARCHAR(45) NOT NULL,
  `location` VARCHAR(45) NOT NULL,
  `category` VARCHAR(45) NOT NULL,
  `amount` DOUBLE NULL, NOT NULL,
  `p_created` TIMESTAMP NOT NULL,
  PRIMARY KEY (`ID`));

Я добавил уникальный ключ для пользователя, местоположения и категории как index2.

я бы хотел

INSERT a new record, if amount is not zero
UPDATE an existing record, if key (column user, location, category) exists
DELETE a record, if key (column user, location, category) exists AND amount is now zero



try {
  $conec = new Connection();
  $con = $conec->Open();
  $p_created = date("Y-m-d H:i:s");
  $sql = "INSERT INTO `pledge`(
    `user`,
    `location`,
    `category`,
    `amount`,
    `p_created`)
    VALUES (
    :user,
    :location,
    :category,
    :amount,
    :p_created)";

  $pre = $con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
  if ($pre->execute(array(
    ':user' => $user,
    ':location' => $location,
    ':category' => $category,
    ':amount' => $amount,
    ':p_created' => $p_created,
  ))) {
//      echo "Successful";
    }
  } catch (PDOException $ex) {
    echo $ex->getMessage();
  }
} // try

Я обнаружил, что могу использовать что-то вроде «ON DUPLICATE KEY UPDATE col3 = 'alpha';», но не знаю, как в приведенном выше php-коде. Поскольку это существует, может быть также возможность удалить вместо обновления, если сумма равна нулю.

Всего 1 ответ


Есть несколько вариантов, каждый с за и против

1) Самое простое, как сказал Ник, это комментарии - это два запроса и три запроса. Не делайте этого - если у вас есть несколько вызовов одновременно, тогда другой пользователь может попытаться обновить одну и ту же запись между чтением и записью.

1b) Если вы настаивали на таком подходе, вы могли бы использовать транзакции. https://dev.mysql.com/doc/refman/8.0/ru/commit.html Однако существуют риски, связанные с транзакциями, которые вам необходимо обработать, и это немного излишне для этой операции.

2) Вторым самым простым (и я бы это сделал) является выполнение «ON DUPLICATE KEY», как вы предлагаете в вопросе, а затем вторая операция «delete if zero». Таким образом, даже если два события перекрываются, они фактически не позволяют друг другу удалить запись, или оба обновляют одну и ту же запись.

Критическое примечание 1: вам нужен УНИКАЛЬНЫЙ индекс для user чтобы это работало. Или удалите «id» в качестве поля и используйте «user» в качестве основного ключа.

Критическое примечание 2: вам не хватает математики в SQL:

$sql = "INSERT INTO `pledge`(`user`, `location`, `category`, `amount`, `p_created`)
    VALUES (:user, :location, :category, :amount, :p_created)
    ON DUPLICATE KEY UPDATE `amount`=`amount`-VALUES(amount)";

который говорит, что введите сумму, если она не существует, в противном случае, сделайте совпадение и обновите запись.

Тогда отдельный запрос

$sql = "DELETE FROM `pledge` WHERE `user`=:user AND `amount`=0";

(«User = 0» не требуется, но поскольку у вас есть пользовательский индекс, у вас нет индекса для «amount», это будет быстрее. Вы можете просто очистить с помощью $sql = "DELETE FROM pledge WHERE сумма =0";)

3) Самый причудливый способ сделать это - использовать триггер внутри базы данных. Вы можете сообщить SQL-серверу, что «если сумма равна нулю, то удалить». Или смешайте все это в хранимой процедуре, но они оба также излишни.


Есть идеи?

10000