mysql - 插入具有两个条件的重复键更新

Question

这是我第一篇文章的编辑版本，因为第一篇似乎不够清晰。

如果存在，我想根据 SELECT 查询结果插入或更新。这是我以标准方式工作的代码:

$query = "SELECT * FROM table1 WHERE id1 =".$id1." AND id2=".$id2;
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
    $query = "UPDATE table1 SET id3=".$id3." WHERE id1 =".$id1." AND id2 = ".$id2." LIMIT 1";
    $result = mysqli_query($conn, $query);
} else {
    $query = "INSERT INTO table1 (id1, id2, id3) VALUES ($id1, $id2, $id3)";
    $result = mysqli_query($conn, $query);
}

id1、id2、id2值可以重复，但必须在一个条件下(id1和id2不能同时重复。

例如:

只有在这种情况下才能插入新行:

如果这个已经存在于table1中

id0=0 id1=5 id2=10 id3=15

那么下一个就是:

id0=1 id1=6 id2=10 id3=15 或

id0=1 id1=5 id2=11 id3=15

当

行会被更新

如果这个已经存在于table1中

id0=0 id1=5 id2=10 id3=15

下一个是:

id0=1 id1=5 id2=10 id3=20

您注意到 id1 和 id2 与表中已有的相同，因此 id3 将被更新。

我的table1 看起来也是这样的:

CREATE TABLE table1 (
  `id0` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `id3` int(11) NOT NULL
) ENGINE=InnoDB;

我的问题是:是否可以使用“在重复键更新时插入”或任何其他方式将上述 3 个查询合并为一个？

Answer 1

首先需要设置key，id1和id2为一个(组合唯一)

CREATE TABLE table1 (
  `id0` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `id3` int(11) NOT NULL,
  UNIQUE KEY `KeyName` (`id1`,`id2`) # <------
) ENGINE=InnoDB;

这样，您就无法添加包含相同 'id1' 和 'id2' 的两行，然后才能使用以下命令。

INSERT INTO table1 (id1, id2, id3) 
            VALUES ($id1, $id2,$id3)
ON DUPLICATE KEY UPDATE id3 = $id3;

更新

当我们在 innoDB 表上执行此方法时，有一个错误会在 AUTO_INCREMENT

中产生间隙

如果我们使用 InnoDB

    CREATE TABLE table1 (
      `id0` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `id1` int(11) NOT NULL,
      `id2` int(11) NOT NULL,
      `id3` int(11) NOT NULL,
      UNIQUE KEY `KeyName` (`id1`,`id2`) # <------
    ) ENGINE=InnoDB;

    INSERT INTO table1 (id1, id2, id3) 
                VALUES (5, 10,1)
    ON DUPLICATE KEY UPDATE id3 = 1;

    INSERT INTO table1 (id1, id2, id3) 
                VALUES (5, 10,2)
    ON DUPLICATE KEY UPDATE id3 = 2;

    INSERT INTO table1 (id1, id2, id3) 
                VALUES (6, 10,1)
    ON DUPLICATE KEY UPDATE id3 = 1;

让结果如下:

+-----+-----+-----+-----+
| id0 | id1 | id2 | id3 |
+-----+-----+-----+-----+
| 1   | 5   | 10  | 2   |
+-----+-----+-----+-----+
| 3   | 6   | 10  | 1   |
+-----+-----+-----+-----+

但是如果我们使用Myisam

    CREATE TABLE table2 (
      `id0` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `id1` int(11) NOT NULL,
      `id2` int(11) NOT NULL,
      `id3` int(11) NOT NULL,
      UNIQUE KEY `KeyName` (`id1`,`id2`) # <------
    ) ENGINE=Myisam;

    INSERT INTO table2 (id1, id2, id3) 
                VALUES (5, 10,1)
    ON DUPLICATE KEY UPDATE id3 = 1;

    INSERT INTO table2 (id1, id2, id3) 
                VALUES (5, 10,2)
    ON DUPLICATE KEY UPDATE id3 = 2;

    INSERT INTO table2 (id1, id2, id3) 
                VALUES (6, 10,1)
    ON DUPLICATE KEY UPDATE id3 = 1;

让结果如下:

+-----+-----+-----+-----+
| id0 | id1 | id2 | id3 |
+-----+-----+-----+-----+
| 1   | 5   | 10  | 2   |
+-----+-----+-----+-----+
| 2   | 6   | 10  | 1   |
+-----+-----+-----+-----+

但这个错误不应该很烦人，如果有必要使用 InnoDB，这只会重新编号行，程序应该知道有间隙，这与删除一行的效果相同。

虽然可以用php来执行，但是只会增加服务器的负载。