MYSQL ON DUPLICATE KEY UPDATE 问题 [英] MYSQL ON DUPLICATE KEY UPDATE question

问题描述

我对 ON DUPLICATE KEY UPDATE 的作用有点困惑.我正在寻找的是将检查 INSERT INTO SQL 命令的东西，如果任何行是重复的，请不要更新该行.现在，如果该行中的任何内容都不是重复的(但有些是)，我想用更新的信息替换该行.

I'm a little bit confused as to what ON DUPLICATE KEY UPDATE does. What I'm looking for is something that will check an INSERT INTO SQL command and if any row is a duplicate do not update that row. Now if anything in that row is not a duplicate (but some is), I'd like to replace that row with the updated information.

这是否可以使用基本的 MYSQL 或我必须先提取所有数据，然后交叉检查.我宁愿不这样做，因为我只想每天缓存一次相当数量的数据.

Is this possible using basic MYSQL or am I going to have to pull all the data first, then cross check it. I'd rather not do that as all I'm trying to do is cache a decent amount of data once a day.

"INSERT INTO years (date,year,venue,city,state,country,showid) VALUES (?,?,?,?,?,?,?)"

推荐答案

ON DUPLICATE KEY UPDATE 只需执行您提供给它的 SET 语句，以防出现重复钥匙.它不会比较单个列的值，而只会更新不同的值.只要您将正确的列定义为 UNIQUE KEY 或 PRIMARY KEY，它听起来确实可以用于您想要做的事情.

ON DUPLICATE KEY UPDATE simply performs the SET statements you provide to it in the case of a duplicate key. It does not compare individual column values and only update the differing ones. It does sound like it will work for what you want to do as long as you have the proper column(s) defined as UNIQUE KEY or PRIMARY KEY.

但是，我通常做的是运行插入，然后捕获错误并在需要时执行不同的操作.如果有重复，这有发出 2 个查询的缺点，但在我看来，它更易于维护.

However, what I normally do is run the insert and then catch the error and perform a differing action if I need to. This has the down side of issuing 2 queries if there is a duplicate but in my opinion it's much more maintainable.

示例:

$db = new PDO($dsn, $user, $pass);
$stmt = $db->prepare('INSERT INTO some_tbl (col1,col2,col3) VALUES (?,?,?)');
$values = array('Col 1 value','Col 2 Value', 'Col 3 Value');
try {
  $db->execute($values);
} catch (PDOException $e) {
  if($e->getCode() == 23000){
    // dupe key do some other action whether update or otherwise
  } else {
    // rethrow non dupe errors
    throw $e;
  }
}

这篇关于MYSQL ON DUPLICATE KEY UPDATE 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！