sql – 有条件的重复键更新

我的桌子由以下列组成：帽子,手套,名称,last_update

帽子手套组成独特的索引(说“帽子”和“手套”的值是颜色)

我们假设这已经在表中：

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

在一个新的关键,我想像往常一样插入.在重复键上,只要名字发生变化,我想做一个更新,否则忽略.原因是我想保留last_update值(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

这是否可能没有使用单独的语句来首先查找现有的行,比较值,然后如果需要发出更新？如果是这样,语法是什么？

感谢您的回应.我试过了所有这些.的确,只使用简单的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,'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版本(也许这很重要？)
再次感谢所有答复.

解决方法

INSERT INTO tbl (hat,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,'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;

这将只会在名称更改时将update_update更新为now(),否则它将告诉MySQL保留last_update的值.

此外,在该语句的ON DUPLICATE KEY部分中,您可以通过其名称引用表中的列,您可以使用VALUES(column_name)函数获取您提供给insert语句值部分的值.

以下是一个日志,显示提供的最后一个语句即使在4.1中,其他人由于5.0版中已修复的错误而无法正常工作.

C:mysqlbin>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,'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,'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,'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,'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,

-Dipin

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!