mysql 以重复密钥更新为条件

ht4b089n 于 2023-03-11 发布在 Mysql

关注(0)|答案(5)|浏览(147)

我试图插入一个新行，但是如果键已经存在，我只想在表中的某个值不同时更新该行。这在mysql查询/语句中可能吗？
我的表由以下几列组成：帽子，连指手套，姓名，上次更新
hat+mittens构成唯一索引（假设“hat”和“mittens”的值是颜色）
我们假设这已经在表中：

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版（也许这很重要？）再次感谢所有的回复。

mysql

来源：https://stackoverflow.com/questions/1044874/conditional-on-duplicate-key-update

5条答案

按热度按时间

kadbb4591#

您可以在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的行为，即如果你给它自己设置一个值，它不会更新一行，你可以执行以下操作来强制时间戳：

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>

如果你有任何问题请告诉我。
嘿，

迪平

赞(0）回复(0）举报 2023-03-11

lb3vh1jj2#

您需要INSERT ... ON DUPLICATE KEY UPDATE语法。
您的查询如下所示：

INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george')
    ON DUPLICATE KEY UPDATE name='george';

如果你已经有一个blue/绿色/乔治的hat/mittens/name记录，实际上不会执行UPDATE，你的时间戳也不会更新。但是如果你有一个blue/green/betty的记录，那么'betty'将被'george'覆盖，你的时间戳也会更新。

赞(0）回复(0）举报 2023-03-11

xxslljrj3#

如果要执行多个INSERT（通过SELECT或向VALUES提供多个行），可以执行以下操作：

INSERT INTO tbl (hat,mittens,name) VALUES
    ('yellow','purple','jimmy'),
    ('blue','green','george'),
    ('blue','green','betty')
ON DUPLICATE KEY UPDATE name = VALUES(name);

这将：