以重复的密钥更新为条件

小编典典

以重复的密钥更新为条件

sql

我正在尝试插入新行，但是如果键已经存在，则仅在表中的某个其他值不同时才想更新行。这可能在mysql查询/语句中吗？

我的表包含以下几列：帽子，手套，名称，last_update

帽子+手套组成唯一索引（例如，“帽子”和“手套”的值是颜色）

让我们假设这已经在表中了：

1. hat=blue mittens=green name=george last_update=tuesday
2. hat=red mittens=green name=bill last_update=monday

在新密钥上，我想照常插入。对于重复键，我只想在名称更改时进行更新，否则忽略。原因是我要保留last_update值（时间戳）。

hat=yellow mittens=purple name=jimmy -- insert new row
hat=blue mittens=green name=george -- ignore 
hat=blue mittens=green name=betty -- update row

是否可以在不使用单独的语句先查找现有行，比较值然后在必要时发出更新的情况下实现？如果是这样，语法是什么？

感谢您的回复。我尝试了所有这些。确实，仅使用一个简单的UPDATE语句，例如

update tbl set name='george' where hat='blue' and mittens='green'

导致没有行被更新。但是，无论使用

 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;

以某种方式导致该行被更新（和时间戳更改）。

FWIW，这是我正在使用的表：

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版（也许这很重要吗？）同样，我感谢所有答复。

阅读 202

2021-04-15

共1个答案

小编典典

您可以在ON DUPLICATE KEY语法中使用普通的sql构造。因此，为了在插入过程中进行条件更新，您可以执行以下操作：

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时间戳。

如果您想100％确保您不依赖MySQL的行为（如果您将值设置为自身，则MySQL不会更新行），您可以执行以下操作来强制时间戳记：

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。

另外，在语句的ON DUPLICATE
KEY部分中，您可以按表的名称引用表中的列，并可以使用VALUES（column_name）函数获取提供给insert语句值部分的值。

以下是一条日志，显示提供的最后一条语句即使在4.1上也可以使用，而其他语句由于在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>

如果您有任何疑问，请告诉我。

HTH，

2021-04-15