MySQL中replace主键_带有多个主键的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...

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

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

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:

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.

Hope this helps!