mysql - 如果列值为 null 或为空，则在 mysql 表中搜索时忽略该列

Question

SELECT logic_id
FROM business_logic_details
WHERE if(form_completion != 0.00,form_completion,0) = '90'
  AND if(query_type IS NOT NULL
         OR query_type !='',query_type,0) LIKE '%domestic%'
  AND if(client_type IS NOT NULL
         OR client_type !='',client_type,0) LIKE '%existing%'
  AND if(tour_package IS NOT NULL
         OR tour_package !='',tour_package,0) LIKE '%S%'
  AND if(tour_type IS NOT NULL
         OR tour_type !='',tour_type,0) LIKE '%2%'
  AND if(currency IS NOT NULL
         OR currency !='',currency,0) = 'INR'
  AND if(country IS NOT NULL
         OR country !='',country,0) = '105'
  AND if(adults IS NOT NULL
         OR adults !='',adults,0) = '1'
  AND if(duration_of_stay IS NOT NULL
         OR duration_of_stay !='',duration_of_stay,0) = '5'
ORDER BY logic_id ASC

Answer 1

使用 COALESCE 函数:

SELECT logic_id
FROM business_logic_details
WHERE COALESCE(form_completion, 90) = 90
  AND COALESCE(query_type, 'domestic') LIKE '%domestic%'
        ...

我还强烈建议将整数作为整数进行比较，而不是作为字符串进行比较:

好:0 = 90
不好:0 = '90'