Mysql更新违反组合键

Question

我想执行一个简单的更新。只是为了增加一些数量到一列，但我有复合键的问题。

--given this table
CREATE TABLE `user_amounts` (   
`id` bigint NOT NULL AUTO_INCREMENT, 
`amount` decimal(19,2) DEFAULT NULL,   
`user_id` bigint NOT NULL,   
PRIMARY KEY (`id`), 
UNIQUE KEY `UK25hzjmhhedgmgy936lfanet49` (`user_id`,`amount`) 
);
--given those values
insert into user_amounts values (1, 0.03, 1), (2, 0.05, 1);
--when I try to increase amounts I cannot because of the unique constraint
update user_amounts set amount = amount + 0.02;

我知道我可以移除约束来执行这个。但是我想知道是否有其他的方法来做到这一点，因为最终在更新后条件得到满足。

Answer 1

Try this - '如果UPDATE语句包含ORDER BY子句，则按子句指定的顺序更新行'

CREATE TABLE `user_amounts` (   
`id` bigint NOT NULL AUTO_INCREMENT, 
`amount` decimal(19,2) DEFAULT NULL,   
`user_id` bigint NOT NULL,   
PRIMARY KEY (`id`), 
UNIQUE KEY key1(`user_id`,`amount`) 
);
insert into user_amounts values (1, 0.03, 1), (2, 0.05, 1);
select * from user_amounts;
update user_amounts set amount = amount + 0.02
order by user_id,amount desc;
select * from user_amounts;
+----+--------+---------+
| id | amount | user_id |
+----+--------+---------+
|  1 |   0.05 |       1 |
|  2 |   0.07 |       1 |
+----+--------+---------+
2 rows in set (0.001 sec)

如果UPDATE语句包含ORDER BY子句，https://dev.mysql.com/doc/refman/8.0/en/update.html -