mysql使用多个主键进行REPLACE查询

so MYSQL's REPLACE command (not to be confused with the string replace function) replaces a row if there exist a column with the same primary key with the inserted data...

因此，如果存在与插入数据具有相同主键的列，则MYSQL的REPLACE命令（不要与字符串替换函数混淆）替换行...

but what if I have two primary keys and I want to use both to specify the row to replace not just one of them....how do I specify mysql to use both keys rather than just one

但是，如果我有两个主键并且我想使用它们来指定要替换的行而不仅仅是其中一个...如何指定mysql以使用两个键而不仅仅是一个

1 个解决方案

#1

It shouldn't make a difference, it's the same syntax. Just be sure you have both keys specified as columns. For example:

它应该没有区别，它是相同的语法。请确保将两个键都指定为列。例如：

REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` /* , ... */ )
  VALUES ( 'widgets', 14, 'Blue widget with purple trim' );

EDIT

编辑

Here's my test I ran in my test database to make sure I wasn't about to destroy your data. Of course, I encourage you to try it out if you're unsure!

这是我在测试数据库中运行的测试，以确保我不会破坏您的数据。当然，如果你不确定，我鼓励你试一试！

CREATE SCHEMA `my_testdb`;
USE `my_testdb`;
CREATE TABLE `my_table` (
  `key1` VARCHAR(20) NOT NULL,
  `key2` INTEGER NOT NULL,
  `othercolumn1` VARCHAR(50),
  CONSTRAINT PRIMARY KEY (`key1`, `key2`) );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'Green widget with fuchsia trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 15, 'Yellow widget with orange trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'thingamabobs', 14, 'Red widget with brown trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'Blue widget with purple trim' );
SELECT * FROM `my_table`;

This is my result:

这是我的结果：

key1          key2  othercolumn1
widgets       14    Blue widget with purple trim
widgets       15    Yellow widget with orange trim
thingamabobs  14    Red widget with brown trim

ANOTHER EDIT

另一个编辑

I think I see what you're talking about in the documentation, the confusion over unique columns:

我想我在文档中看到了你在谈论的内容，对独特列的困惑：

It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes. —MySQL Documentation

如果表包含多个唯一索引，并且新行为不同唯一索引中的不同旧行重复值，则单行可以替换多个旧行。 -MySQL文档

That's referring to a rather contrived circumstance in which the row you're replacing with conflicts not just with an existing primary key, but with other unique columns as well. Here's another example to illustrate this point:

这是指一个相当人为的情况，在这种环境中，您要替换的行不仅与现有主键冲突，而且还与其他唯一列冲突。这是另一个说明这一点的例子：

CREATE SCHEMA `my_testdb2`;
USE `my_testdb2`;
CREATE TABLE `my_table` (
  `key1` VARCHAR(20) NOT NULL,
  `key2` INTEGER NOT NULL,
  `color` VARCHAR(20) NOT NULL UNIQUE,
  `othercolumn1` VARCHAR(50),
  CONSTRAINT PRIMARY KEY (`key1`, `key2`) );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'green', 'Green widget with fuchsia trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 15, 'yellow', 'Yellow widget with orange trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'thingamabobs', 14, 'red', 'Red widget with brown trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'yellow', 'Yellow widget with purple trim' );
SELECT * FROM `my_table`;

Notice how the last REPLACE operation not only conflicts with the (key1, key2) primary key, of the first REPLACE, but also with the unique color of the second one. In this case, BOTH rows are deleted before the last REPLACE operation is performed so that the result is no conflict. You'll end up with just two rows:

注意最后一个REPLACE操作不仅与第一个REPLACE的（key1，key2）主键冲突，而且还与第二个REPLACE的唯一颜色冲突。在这种情况下，在执行最后一次REPLACE操作之前删除BOTH行，以便结果不冲突。你最终只会有两行：

key1          key2  color   othercolumn1
widgets       14    yellow  Yellow widget with purple trim
thingamabobs  14    red     Red widget with brown trim

Both the row with (key1, key2) equal to ('widgets', 14) AND the row with the color 'yellow' were blown away due to the new row conflicting with multiple unique constraints on the table.

由于新行与表上的多个唯一约束冲突，（key1，key2）等于（'widgets'，14）的行和颜色为'yellow'的行都被吹走了。

Hope this helps!

希望这可以帮助！

#1