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

2022-01-10 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),
     ...

相关文章