mysql时间null比较,MySQL使用BETWEEN与NULL比较

I have a query with a where condition like so:

WHERE A.event_date BETWEEN B.start_date AND B.end_date

The complexity is that if B.start_date is NULL, it means from time immemorial, similarly B.end_date is NULL, it means the present. So I still want to select a row where A.event_date > B.start_date and B.end_date is NULL, for example.

The long-winded way to do this is

WHERE A.event_date BETWEEN B.start_date AND B.end_date

OR (A.event_date > B.start_date AND B.end_date IS NULL)

OR (B.start_date IS NULL AND A.event_date < B.end_date)

Is there a more elegant solution (especially since in one query I have multiple between condition like that)

解决方案

IFNULL might help out here:

WHERE A.event_date BETWEEN IFNULL(B.start_date,"1900-01-01")

AND IFNULL(B.end_date,now());