Mysql如何筛选有效期内优惠：到期日为NULL/空时始终展示

筛选规则 #

• 当前时间晚于等于活动开始时间
• 若活动到期日期非空且不为NULL，当前时间需早于到期时间
• 若到期日期为NULL或空值，活动始终可展示

现有SQL核心问题 #

1. 逻辑运算符优先级错误：MySQL中AND运算优先级高于OR，原语句的到期时间为空判断会直接绕过前面的活动状态、开始时间校验，只要到期时间为空就会被查询出来，不符合筛选要求
2. 开始时间判断逻辑反向：原条件deals.deal_start >= 当前时间是筛选还未开始的活动，和要求的「当前时间晚于等于活动开始时间」逻辑相反

表结构 #

按时区获取当前时间的PHP函数 #

function getDateByTimeZone(){
   $date = new DateTime("now", new DateTimeZone("Europe/London") );
   return $date->format('m-d-Y H:i:s');
}

修正后的MySQL查询语句 #

SELECT deals.*, categories.category_title AS category_title 
FROM deals 
LEFT JOIN categories ON deal_category = categories.category_id 
WHERE deals.deal_status = 1 
 AND deals.deal_featured = 1 
 AND '" . getDateByTimeZone() . "' >= deals.deal_start
 AND (
    '" . getDateByTimeZone() . "' < deals.deal_expire 
    OR deals.deal_expire IS NULL 
    OR deals.deal_expire = ''
 )
GROUP BY deals.deal_id ORDER BY deals.deal_created DESC

内容的提问来源于stack exchange，提问作者bdroid