mysql - if语句: when date field is 1 month or older

Question

我有一个名为 transactions 的表有 5 个字段。我正在运行 update查询列 where name =jane并递增transaction通过1 。当 date_created – tran_date = 1 month 时如何使用 if 语句并将值设置为 transaction = 0 , tran_date = 0000-00-00 ，并更改 date_created到新的当前日期。

查询

UPDATE transactions SET transactions = transactions + 1, tran_date = CURDATE()  WHERE name = 'jim'

创建表并设置值:

CREATE TABLE transactions
(
  id int auto_increment primary key,
  date_created DATE,
  name varchar(20),
  transactions int(6), 
  tran_date DATE
);

INSERT INTO transactions
(date_created, name, transactions, tran_date)
VALUES
(NOW(), 'jim', 0, 0000-00-00),
(NOW(), 'jane', 0, 0000-00-00);

Answer 1

一种方法是使用 case 语句:

UPDATE transactions
    SET transactions = (case when tran_date >= date_sub(CURDATE(), interval 1 month)
                             then transactions + 1 else 0
                        end),
        tran_date = (case when tran_date >= date_sub(CURDATE(), interval 1 month)
                          then CURDATE() 
                          else '0000-00-00'
                     end),
        date_created = (case when tran_date >= date_sub(CURDATE(), interval 1 month)
                             then date_created 
                             else CURDATE()
                         end)
    WHERE name = 'jim';

另一种方法是使用两个单独的更新来完成此操作:

UPDATE transactions
    SET transactions = transactions + 1 
    WHERE name = 'jim' and tran_date >= date_sub(CURDATE(), interval 1 month)

UPDATE transactions
    SET transactions = 0,
        tran_date = '0000-00-00',
        date_created = CURDATE()
    WHERE name = 'jim' and tran_date < date_sub(CURDATE(), interval 1 month)

我认为逻辑可能更清晰一些，但是两个 update 语句的开销更大。