以重复的密钥更新为条件 [英] conditional on duplicate key update
问题描述
我正在尝试插入新行，但是如果键已经存在，那么我只想在表中的某个其他值不同的情况下才更新行.在mysql查询/语句中有可能吗?
I'm trying to insert a new row, but if the key already exists, I want to update the row ONLY if a certain other value is in the table is different. Is this possible in a mysql query/statement?
我的表由以下几列组成:帽子，手套，名称，last_update
My table consist of the following columns: hat, mittens, name, last_update
帽子+手套组成唯一索引(例如，帽子"和手套"的值是颜色)
hat+mittens make up the unique index (say the values for "hat" and "mittens" are colors)
让我们假设它已经在表中了:
Let's assume this is already in the table:
1. hat=blue mittens=green name=george last_update=tuesday
2. hat=red mittens=green name=bill last_update=monday
在新密钥上，我想照常插入.对于重复键，我只想在名称更改时进行更新，否则忽略.原因是我要保留last_update值(时间戳).
On a new key, I want to insert as usual. On duplicate key, I want to do an update ONLY IF the name changes, otherwise ignore. The reason for this is that I want to preserve the last_update value (timestamp).
hat=yellow mittens=purple name=jimmy -- insert new row
hat=blue mittens=green name=george -- ignore
hat=blue mittens=green name=betty -- update row
是否可以在不使用单独的语句的情况下先查找现有行，比较值然后在必要时发出更新?如果是这样，语法是什么?
Is this possible without using separate statements to first look up the existing row, compare values and then issue an update if necessary? If so, what would be the syntax?
感谢您的回复.我尝试了所有这些.实际上，仅使用一个简单的UPDATE语句(例如
Thanks for your responses. I tried all of them. Indeed, using just a simple UPDATE statement like
update tbl set name='george' where hat='blue' and mittens='green'
不会导致任何行被更新.但是，使用
results in no rows being updated. But, using either
INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name='george';
或
INSERT INTO tbl (hat, mittens, name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name=CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END;
以某种方式导致该行被更新(并且时间戳被更改).
somehow results in the row being updated (and the timestamp changed).
FWIW，这是我正在使用的表格:
FWIW, this is the table I'm using:
CREATE TABLE `tbl` (
`hat` varchar(11) default NULL,
`mittens` varchar(11) default NULL,
`name` varchar(11) default NULL,
`stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
UNIQUE KEY `clothes` (`hat`,`mittens`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
MySQL是4.1.22版(也许这很重要吗?) 再次感谢所有回复.
MySQL is version 4.1.22 (perhaps this matters?) Again, my appreciation for all of the replies.
推荐答案
您可以在语法.因此，为了在插入过程中进行条件更新，您可以执行以下操作:
You can use normal sql constructs in the ON DUPLICATE KEY syntax. So in order to do conditional updates during an insert you can do the following:
INSERT INTO tbl (hat, mittens, name)
VALUES ('yellow','purple','jimmy')
ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name)
THEN VALUES(name) ELSE name END;
这会将值更改为您提供给insert语句的值(与行中的值不同)，如果未更改，则将值设置为已经存在的值，并导致MySQL不执行任何操作Quassnoi指出，保留last_update时间戳的行.
This will change the value to what you provided to the insert statement when it's different from what's in the row and will set the value to be what it already is if it hasn't changed and will result in MySQL not doing anything to the row preserving the last_update timestamp as Quassnoi pointed out.
如果您想100％确保您不依赖MySQL的行为(如果您将值设置为自身，则MySQL不会更新行)，您可以执行以下操作来强制时间戳记:
If you wanted to make 100% sure that you weren't relying on the behavior of MySQL where it doesn't update a row if you set a value to itself you can do the following to force the timestamp:
INSERT INTO tbl (hat, mittens, name)
VALUES ('yellow','purple','jimmy')
ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name)
THEN VALUES(name) ELSE name END
, last_update = CASE WHEN name <> VALUES(name)
THEN now() ELSE last_update END;
这只会在名称更改后将last_update更新为now()，否则它将告诉MySQL保留last_update的值.
This will only update the last_update to now() when the name has changed else it will tell MySQL to retain the value of last_update.
此外，在语句的ON DUPLICATE KEY部分中，您可以通过表的名称引用表中的列，并可以使用
Also, in the ON DUPLICATE KEY section of the statement you can refer to the columns in the table by their name and you can get the values that you provided to the insert statement values section using the VALUES(column_name) function.
以下是一条日志，该日志显示所提供的最后一条语句即使在4.1上也可以使用，而其他语句由于版本5.0中已修复的错误而无法使用.
The following is a log that shows that the last statement provided works even on 4.1 where the others don't work due to a bug that was fixed in version 5.0.
C:\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-community
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE `tbl` (
-> `hat` varchar(11) default NULL,
-> `mittens` varchar(11) default NULL,
-> `name` varchar(11) default NULL,
-> `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> UNIQUE KEY `clothes` (`hat`,`mittens`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat | mittens | name | stamp |
+------+---------+--------+---------------------+
| blue | green | george | 2009-06-27 12:15:16 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name='george';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat | mittens | name | stamp |
+------+---------+--------+---------------------+
| blue | green | george | 2009-06-27 12:15:30 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO tbl (hat, mittens, name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name=CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat | mittens | name | stamp |
+------+---------+--------+---------------------+
| blue | green | george | 2009-06-27 12:15:42 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END, stamp = CASE WHEN name <> VALUES(name) THEN now() ELSE stamp END;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat | mittens | name | stamp |
+------+---------+--------+---------------------+
| blue | green | george | 2009-06-27 12:15:42 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)
mysql>
如果您有任何疑问，请告诉我.
Let me know if you have any questions.
HTH，
-Dipin
这篇关于以重复的密钥更新为条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
