如何更新MySQL/MariaDB中指定了最新值的日期类型列

Question

我正在通过Spring Boot的JPA依赖关系处理我的MariaDB。

我有一个表，看起来像下面描述的表

因此，id、date和ip字段是此表的复合主键。

我试图使用以下查询更新匹配结果中最新的日期

sql prettyprint-override">UPDATE my_table SET date = NOW() WHERE
   id = 'finding_id' AND
   ip = 'finding_ip';

由于数据中存在重复的id-ip配对条目，如下所示，

我收到一条错误消息。

作为临时措施，我在where子句中附加了另一个限制，以将时间范围缩小为“在最后15分钟内”。

但是我正在为这个问题寻找更优雅的解决方法。

Answer 1

这里的问题是数据库模式。

您有一个主键，它是一个由3列组成的唯一复合键，并且希望能够在其中包含非唯一集。

除非您想每次都将Date更新为一个新的补丁（添加/包含毫秒），否则这是不可能的。

由于您似乎不关心这3列的唯一性，我建议您将索引类型从PRIMARY更改为index（仍然是composite），这样您就没有唯一性约束。

之后，添加一个新的“real”id列（或重命名另一列，使其成为id列），该列仅为启用自动递增的普通主键。

Answer 2

在MySQL和MariaDB中，可以在update语句中使用ORDER BY和LIMIT：

UPDATE my_table 
SET `date` = NOW() 
WHERE
   id = 'finding_id' AND
   ip = 'finding_ip'
ORDER BY `date` DESC
LIMIT 1;

由于WHERE子句的存在，上述操作将使用当前日期时间更新过滤器中日期最长的记录。

注意：最好不要将任何保留字用作列名。可以，但必须在字段名周围使用反勾号。