带有多个主键的mysql REPLACE查询 [英] mysql REPLACE query with multiple primary keys

问题描述

因此，如果存在与插入的数据具有相同主键的列，则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同时使用两个主键而不是一个

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

推荐答案

应该没什么不同，它是相同的语法.只要确保您将两个键都指定为列即可.例如:

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' );

编辑

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

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`;

这是我的结果:

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

另一项编辑

我想我在文档中看到了您在说什么，对唯一列的困惑:

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

如果表包含多个唯一索引，并且新行复制了不同唯一索引中不同旧行的值，则单行可以替换一个以上旧行. — 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`;

请注意，最后一个REPLACE操作不仅与第一个REPLACE的(key1，key2)主键冲突，而且与第二个REPLACE的唯一颜色冲突.在这种情况下，在最后一次执行REPLACE操作之前，BOTH行都将被删除，因此结果不会发生冲突.您将只剩下两行:

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:

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

(key1，key2)等于('widgets ,, 14) AND 的行都被吹掉了，因为新行冲突表格上有多个唯一约束.

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.

希望这会有所帮助！

这篇关于带有多个主键的mysql REPLACE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！