mysql - 是否可以将其合并为一个查询？

Question

我正在更新 2 列和多行。目前我正在运行两个不同的查询来完成这项工作。是否可以在一个查询中完成？

UPDATE `messages` SET `from_delete` = NOW() WHERE `thread_hash` = 'abc' AND `from_user_id` = '6'

UPDATE `messages` SET `to_delete` = NOW() WHERE `thread_hash` = 'abc' AND `to_user_id` = '6'

Answer 1

是的，这是可能的，但这很棘手:

UPDATE `messages`
   SET `from_delete` = IF(`from_user_id` = '6',NOW(),`from_delete`)
     , `to_delete`   = IF(`to_user_id`   = '6',NOW(),`to_delete`  ) 
 WHERE (`thread_hash` = 'abc' AND `from_user_id` = '6')
    OR (`thread_hash` = 'abc' AND `to_user_id`   = '6')

“技巧”是在赋值中使用条件。如果不应更新该行，则将列的当前值分配给该列以进行“无更改”操作。

为了完整性，您可能希望将 thread_hash 上的条件添加到条件中。虽然这不会改变查询的任何内容:

UPDATE `messages`
   SET `from_delete` = IF(`thread_hash` = 'abc' AND `from_user_id` = '6'
                       ,NOW(),`from_delete`)
     , `to_delete`   = IF(`thread_hash` = 'abc' AND `to_user_id`   = '6'
                       ,NOW(),`to_delete`  ) 
 WHERE (`thread_hash` = 'abc' AND `from_user_id` = '6')
    OR (`thread_hash` = 'abc' AND `to_user_id`   = '6')