MYSQL DATE_ADD 间隔作为变量或来自另一列

Question

我试图在我的表上创建一个事件，每隔几分钟运行一次，以查看“next_run”是否现在()，如果是，则将“频率”添加到“next_run”，但每次我都会收到语法异常.

下面是表结构...

     LAST_RUN       |FREQUENCY|   NEXT_RUN 
----------------------------------------------------
2016-09-15 06:02:06 | 1 DAY   | 2016-09-15 06:02:06

”我正在使用的代码是这样的...

UPDATE TASKS_MASTER_COPY 
@num:=CAST(FREQUENCY) AS UNSIGNED,
@p  :=SUBSTR(FREQUENCY, CHAR_LENGTH(@num)+2)
LAST_RUN=NEXT_RUN,
NEXT_RUN=NEXT_RUN + CASE
        WHEN @p='YEAR' THEN DATE_ADD(NEXT_RUN, INTERVAL @num YEAR)
        WHEN @p='MONTH' THEN DATE_ADD(NEXT_RUN, INTERVAL @num MONTH)
        WHEN @p='DAY' THEN DATE_ADD(NEXT_RUN, INTERVAL @num DAY)
        WHEN @p='WEEK' THEN DATE_ADD(NEXT_RUN, INTERVAL @num WEEK)
            END
WHERE TASK_MASTER_ID=100;

有人可以帮我解决这个问题吗？提前致谢。

Answer 1

这个版本对我来说不会出错:

UPDATE TASKS_MASTER_COPY
SET
LAST_RUN = NEXT_RUN,
NEXT_RUN = NEXT_RUN +
  CASE
    WHEN SUBSTR(FREQUENCY, CHAR_LENGTH(CAST(FREQUENCY AS UNSIGNED)) + 2) = 'YEAR'
    THEN DATE_ADD(NEXT_RUN, INTERVAL CAST(FREQUENCY AS UNSIGNED) YEAR )
    WHEN SUBSTR(FREQUENCY, CHAR_LENGTH(CAST(FREQUENCY AS UNSIGNED)) + 2) = 'MONTH'
    THEN DATE_ADD(NEXT_RUN, INTERVAL CAST(FREQUENCY AS UNSIGNED) MONTH)
    WHEN SUBSTR(FREQUENCY, CHAR_LENGTH(CAST(FREQUENCY AS UNSIGNED)) + 2) = 'DAY'
    THEN DATE_ADD(NEXT_RUN, INTERVAL CAST(FREQUENCY AS UNSIGNED) DAY  )
    WHEN SUBSTR(FREQUENCY, CHAR_LENGTH(CAST(FREQUENCY AS UNSIGNED)) + 2) = 'WEEK'
    THEN DATE_ADD(NEXT_RUN, INTERVAL CAST(FREQUENCY AS UNSIGNED) WEEK )
  END
WHERE TASK_MASTER_ID = 100;

几个问题:

• CAST(FREQUENCY AS UNSIGNED)不是CAST(FREQUENCY) AS UNSIGNED
• 安 UPDATE需要 SET在表名之后
• 我相信 UPDATE只能包含要更新的列，不能包含@num和@p .

基于https://stackoverflow.com/a/22404258/761771