如何使用字符串/列值作为mysql日期间隔常数（DAY，MONTH ...）？ [英] How to use a string/column value as a mysql date interval constant (DAY, MONTH...)?

问题描述

我有三列：一个日期列，一个整数列和一个这样的varchar列：

I have three columns: a date column, a integer column, and a varchar column like this:

+------------+------+---------+
| date       |value |  unit   |
+------------+------+---------+
| 2009-01-01 |    2 | DAY     | 
| 2009-02-01 |    3 | MONTH   | 
+------------+------+---------+

我想使用mysql date_add（）函数中的整数和varchar列的值作为INTVERAL表达式的一部分，添加到日期列。

I want to use the values of the integer and the varchar column in a mysql date_add() function as part of the "INTVERAL" expression, added to the date in the 'date' column.

例如：
date_add（ 2009-01-01 ，INTERVAL 2 DAY ），所以'2009-01-01'来自'date'列，'2'来自值/整数列，DAY来自单位'/ varchar列。

For example: date_add(2009-01-01, INTERVAL 2 DAY), so that the '2009-01-01' is from the 'date' column, the '2' is from the "value"/integer column and the "DAY" is from the 'unit'/varchar column.

选择将如下所示：

select date_add(table.date_column, INTERVAL table.integer_column table.varchar_column) from table

问题是：它不工作日期和整数列工作，所以这是确定：

Problem is: it doesn't work. The date and the integer column work, so this is ok:

 select date_add(table.date_column, INTERVAL table.integer_column DAY) from table

但只要我尝试用字符串值替换DAY关键字从列中我得到一个错误信息。

but as soon I try to replace the "DAY" Keyword with a string value from a column I get an error message.

任何想法？

问题是：

如何使用动态检索的值作为常量/键表达式？在mysql中是否有一种eval函数？

How do I use dynamically retrieved values as constants/key expressions? Is there a kind of "eval" function in mysql ?

这个问题现在已经很麻烦了，任何帮助都会非常棒。

This problem has been bugging me for a long time now, any help would be really great.

Beat

推荐答案

不幸的是，MySQL期望INTERVAL之后的关键字，而不是任何字符串或数字值。您可以通过使用CASE语句来实现所需的内容，并使用不同的关键字给出不同的情况。

Unfortunately MySQL expects a keyword after INTERVAL and not any string or numeric value. You can achieve what you want by using a CASE statement and give the different cases with the different keywords.

举个例子，假设你要添加适当的单位到日期然后SQL语句将如下所示：

As an example, let's say you want to add the value with the appropriate unit to the date then the SQL statement would be as follows:

SELECT CASE unit
       WHEN "DAY" THEN date_add(date, INTERVAL value DAY)
       WHEN "MONTH" THEN date_add(date, INTERVAL value MONTH)
       END
       AS newDate
FROM table

也可以在WHERE子句中使用：）

Also works in the WHERE clause by the way :)

这篇关于如何使用字符串/列值作为mysql日期间隔常数（DAY，MONTH ...）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！