在单个查询中插入多行的 MySQL ON DUPLICATE KEY UPDATE

2021-11-20 00:00:00 sql duplicates mysql

我有一个 SQL 查询，我想在单个查询中插入多行.所以我使用了类似的东西:

I have a SQL query where I want to insert multiple rows in single query. so I used something like:

$sql = "INSERT INTO beautiful (name, age)
  VALUES
  ('Helen', 24),
  ('Katrina', 21),
  ('Samia', 22),
  ('Hui Ling', 25),
  ('Yumie', 29)";

mysql_query( $sql, $conn );

问题是当我执行这个查询时，我想检查一个 UNIQUE 键(不是 PRIMARY KEY)，例如上面的 'name' 应该检查，如果这样的 'name' 已经存在，则应该更新相应的整行，否则插入.

The problem is when I execute this query, I want to check whether a UNIQUE key (which is not the PRIMARY KEY), e.g. 'name' above, should be checked and if such a 'name' already exists, the corresponding whole row should be updated otherwise inserted.

例如，在下面的示例中，如果 'Katrina' 已经存在于数据库中，则无论字段数量如何，都应该更新整行.同样，如果 'Samia' 不存在，则应插入该行.

For instance, in the example below, if 'Katrina' is already present in the database, the whole row, irrespective of the number of fields, should be updated. Again if 'Samia' is not present, the row should be inserted.

我想过使用:

INSERT INTO beautiful (name, age)
      VALUES
      ('Helen', 24),
      ('Katrina', 21),
      ('Samia', 22),
      ('Hui Ling', 25),
      ('Yumie', 29) ON DUPLICATE KEY UPDATE

这里是陷阱.我被卡住了，对如何继续感到困惑.我一次要插入/更新多行.请给我一个方向.谢谢.

Here is the trap. I got stuck and confused about how to proceed. I have multiple rows to insert/update at a time. Please give me a direction. Thanks.

推荐答案

从 MySQL 8.0.19 开始，您可以为该行使用别名(请参阅 参考).

Beginning with MySQL 8.0.19 you can use an alias for that row (see reference).

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
    AS new
ON DUPLICATE KEY UPDATE
    age = new.age
    ...

对于早期版本，使用关键字 VALUES (参见 参考，在 MySQL 8.0.20 中已弃用.

For earlier versions use the keyword VALUES (see reference, deprecated with MySQL 8.0.20).

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
     ...

相关文章