mysql 字符串 常数,如何使用字符串/列值作为mysql日期间隔常数（DAY，MONTH ...）？...

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 |

+------------+------+---------+

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.

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.

And the select would look like this:

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

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

Any Ideas?

I guess more generally the problem is:

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

解决方案

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.

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

Also works in the WHERE clause by the way :)