使用mysql中的间隔在数据库日期中添加月份

Question

我想通过连接计划表和事务表，使用mysql间隔函数在事务日期中添加月份，但这种方法不起作用，但如果我以静态方式在事务日期上添加月份，它就起作用了。

plan表：

plan_id    plan
1         6 month    
2         12 month    
3         3 month

transaction表：

id  user_id  subscribed_on   plan_id    
1     2       2020-04-04     1    
2     4       2019-02-22     2 

Mysql查询(不工作(：

SELECT t.* FROM transaction t inner join plan p on p.plan_id=t.plan_id 
where t.user_id=2 and DATE_ADD(date(t.subscribed_on), INTERVAL p.plan) >= CURDATE() 
order by t.id desc

如果我以静态方式添加月份，则它运行良好：

SELECT t.* FROM transaction t inner join plan p on p.plan_id=t.plan_id 
where t.user_id=2 and DATE_ADD(date(t.subscribed_on),
INTERVAL 6 month) >= CURDATE() 
order by t.id desc

Answer 1

MySQL不支持以这种方式使用interval。与其他数据库(例如Postgres(不同，unit参数是关键字，而不是文本字符串。

我怀疑您的表可能会存储除月份以外的其他时间间隔(例如年、天等(。如果是这样，您可以使用字符串函数和case表达式来适应不同的可能值，如：

select t.* 
from transaction t 
inner join plan p on p.plan_id = t.plan_id 
where 
t.user_id = 2 
and date(t.subscribed_on) + case substring_index(p.plan, ' ', -1)
when 'year'  then interval substring_index(p.plan, ' ', 1) year
when 'month' then interval substring_index(p.plan, ' ', 1) month
when 'day'   then interval substring_index(p.plan, ' ', 1) day
end
>= current_date
order by t.id desc

这里的逻辑是将存储的间隔字符串拆分为两部分：数字和单位；CCD_ 4表达式处理该单元并相应地生成适当的文字间隔。

Answer 2

不幸的是，数据中的字符串并不等同于间隔。一种方法是：

date(t.subscribed_on) + interval substring_index(plan, ' ') + 0 month

请注意，month是一个关键字，而不是字符串。

Answer 3

尝试强制plan表中的plan列为整数。似乎不可能将字符串强制转换为区间。

我试过了：

WITH
plan( plan_id,plan) AS (
SELECT 1,'6 month'
UNION ALL SELECT 2,'12 month'    
UNION ALL SELECT 3,'3 month'
)
,
transaction(id,user_id,subscribed_on,plan_id) AS (
SELECT 1,2,DATE '2020-09-04',1    
UNION ALL SELECT 2,4,DATE '2019-02-22',2 
)
SELECT t.*
FROM transaction t
INNER JOIN plan p ON p.plan_id = t.plan_id
WHERE t.user_id = 2
AND DATE_ADD(
DATE(t.subscribed_on)
, INTERVAL CAST(REPLACE(plan,' month','') AS SIGNED) MONTH
) >= CURDATE()
ORDER BY t.id DESC

(没有返回结果，因为您的示例数据中没有足够高的日期…(