mysql - SQL IF 逐日存在做增量更新否则插入数据

Question

如何将以下语句表达为 SQL 查询？

IF EXISTS (SELECT * FROM expense_history 
           WHERE user_id = 40 
             AND DATE_FORMAT(expense_history.created_date , '%Y-%m-%d') = '2018-06-02' 
             AND camp_id='80') 
    UPDATE expense_history  
    SET clicks = clicks + 1, 
        amount = amount + 1 
    WHERE user_id = 40 
      AND DATE_FORMAT(expense_history.created_date, '%Y-%m-%d') = '2018-06-02' 
      AND camp_id = '80'
ELSE
   INSERT INTO expense_history (camp_id, created_date, amount, user_id) 
   VALUES ('80', '2018-06-02 12:12:12', '1', '40')
END IF;

如果按天设置，我只想增加点击次数和数量，否则我想添加新行。

Answer 1

这在 MySQL 中非常棘手。您正在存储一个 datetime，但您希望日期部分是唯一的。

从 MySQL 5.7.? 开始，您可以使用计算列作为唯一约束。这是一个例子:

create table expense_history (
  user_id int,
  camp_id int,
  amount int default 0,
  clicks int default 1,
  . . .
  created_datetime datetime,  -- note I changed the name
  created_date date generated always as (date(created_datetime)),
  unique (user_id, camp_id, created_datetime)
);

然后您可以按照以下方式进行工作:

INSERT INTO expense_history (camp_id, created_datetime, amount, user_id) 
    VALUES (80, '2018-06-02 12:12:12', 1, 40)
    ON DUPLICATE KEY UPDATE
        amount = COALESCE(amount + 1, 1),
        clicks = COALESCE(clicks + 1, 1);

早期版本的 MySQL 不支持生成的列。它们也不支持 unique 上的函数。但是你可以在 varchar 的前缀索引上使用一个技巧来做你想做的事:

create table expense_history (
  user_id int,
  camp_id int,
  amount int default 0,
  clicks int default 1,
  . . .
  created_datetime varchar(19), 
  unique (created_datetime(10))
);

这具有相同的效果。

另一种方法是将日期和时间存储在单独的列中。