查询中的变量

Question

UPDATE ost_timeblock 
SET 
  timeblock_due_date=DATE(timeblock_next_update),
  timeblock_next_update=DATE_ADD(NOW(),INTERVAL timeblock_recurrence_time **timeblock_recurrence_unit** )
WHERE timeblock_recurrence=1 
  AND timeblock_complete=0 
  AND timeblock_next_update=FROM_UNIXTIME(1337662800)

当我使用手动天或月作为timeblock_recurrence_unit时，它工作得很好。

timeblock_recurrence_unit = enum(day,month,year)我想要timeblock_recurrence_unit的值在上面的粗体位置

有没有什么方法可以做到这一点。

If timeblock_recurrence_unit = 'day' then 
timeblock_next_update=DATE_ADD(NOW(),INTERVAL timeblock_recurrence_time day )

If timeblock_recurrence_unit = 'month' then  
timeblock_next_update=DATE_ADD(NOW(),INTERVAL timeblock_recurrence_time month)

Answer 1

UPDATE ost_timeblock SET 
    timeblock_due_date = DATE(timeblock_next_update),
    timeblock_next_update = DATE_ADD(NOW(), INTERVAL timeblock_recurrence_time      
**timeblock_recurrence_unit** )
WHERE 
    timeblock_recurrence=1 
AND timeblock_complete=0 
AND timeblock_next_update = FROM_UNIXTIME(1337662800)

根据mysql文档：http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

date_add功能接受日期和单位的倒置，以执行日期运算。您必须提供给定的单位，而不是像您希望的那样提供单位的ENUM。

您将不得不修改您的功能需求，并可能重新考虑DATE_ADD方法是否正在执行所需的操作。

rgds。

Answer 2

您可以按如下方式使用用户定义变量；请参阅http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

SET @timeblock_recurrence_unit = 'DAY';
UPDATE ost_timeblock SET
 timeblock_due_date=DATE(timeblock_next_update),
 timeblock_next_update=DATE_ADD(
       NOW(),
       INTERVAL timeblock_recurrence_time @timeblock_recurrence_unit
 ) WHERE 
    timeblock_recurrence=1 AND 
    timeblock_complete=0 AND
    timeblock_next_update= FROM_UNIXTIME(1337662800);