mysql - 如何在 SQL 查询中设置动态可选 WHERE 子句？ (如果参数的值不为空)

Question

我不太喜欢数据库，并且查询有问题。我正在使用 MySql。

我有一个查询，其中包含一个必需的 WHERE 子句和一些其他可选的 WHERE 子句，如下所示:

WHERE Localization.id = 2
AND(
    Crop.id = 2
    OR
    ProcessPhase.id = 1
    OR
    ProcessPhaseAction.id = 1
    OR
    UserType.id = 1
    OR
    Urgency.id = 1
    OR
    EnutriMessageDetails.provided_by_id = 1
    OR
    EnutriMessageDetails.cleared_by_id = 1
    )

Localization.id是强制子句，其他的AND都是可选的。我是这样实现的，但是我觉得不太好。

我最初的想法是，如果字段的值为空，则从子句列表中删除该特定的 where 子句。

例如，我想使用 AND(避免 OR)放置所有子句，如果我有一些内容:

Crop.id = null

该子句已从子句列表中删除。

我知道我可以使用编程语言来完成此操作，在子句中附加 IF 语句(如果不为空则附加)，但在这种情况下，此查询也不会运行到程序中，所以我不能这样做，所以我必须仅使用 SQL。

如何仅使用 SQL 来完成此操作？

EDIT-1:这是更新的子句版本，但仍然不起作用:

WHERE Localization.id = 2

AND 
    (EnutriMessageDetails.crop_id = 2 OR EnutriMessageDetails.crop_id IS NULL)
AND
    (EnutriMessageDetails.process_phase_id = 2 OR EnutriMessageDetails.process_phase_id IS NULL)
AND
    (EnutriMessageDetails.process_phase_action_id = 2 OR EnutriMessageDetails.process_phase_action_id IS NULL)
AND
    (EnutriMessageDetails.user_type_id = 2 OR EnutriMessageDetails.user_type_id IS NULL)
AND
    (EnutriMessageDetails.urgency_id = 2 OR EnutriMessageDetails.urgency_id IS NULL)
AND
    (EnutriMessageDetails.provided_by_id = 2 OR EnutriMessageDetails.provided_by_id IS NULL)
AND
    (EnutriMessageDetails.cleared_by_id = 2 OR EnutriMessageDetails.cleared_by_id IS NULL)

EDIT-2:这是整个查询:

SELECT 
    EnutrifoodMessage.content
    , MessageType.message_type_name
    , Country.country_name
    , IFNULL(Province.province_name, 'All Provinces') as province_name
    , IFNULL(District.district_name, 'Any District') as district_name
    , Crop.crop_name
    , EnutriMessageDetails.creation_date
    , EnutriMessageDetails.message_important_days
    , temp_scale.scale_name as temperature
    , humidity_scale.scale_name as humidity
    , ProcessPhase.phase_name
    , ProcessPhaseAction.process_phase_action_name
    , Urgency.urgency_name as action
    , IFNULL(MeteoWarningDescription.meteo_warning_description_name, '') as emergency
    , IFNULL(EnutriMessageDetails.internal_link, '') as internal_link
    , IFNULL(EnutriMessageDetails.reference_link, '') as reference_link
    , IFNULL(EnutriMessageDetails.external_link, '') as external_link
    , IFNULL(cleared_by_institution.institution_name, '') as message_cleared_by
    , UserType.user_type_name as end_user
    , provider.institution_name as provider
    , ValueAddition.value_addition_name
FROM EnutriMessageDetails
LEFT JOIN EnutrifoodMessage
ON EnutrifoodMessage.id = EnutriMessageDetails.enutri_food_message_id
LEFT JOIN MessageType
ON MessageType.id = EnutriMessageDetails.message_type_id
LEFT JOIN Localization
ON Localization.id = EnutriMessageDetails.localization_id
LEFT JOIN Country
ON Country.id = Localization.country_id
LEFT JOIN Province
ON Province.id = Localization.province_id
LEFT JOIN District
ON District.id = Localization.district_id
LEFT JOIN Crop
ON Crop.id = EnutriMessageDetails.crop_id
LEFT JOIN Scale temp_scale
ON temp_scale.id = EnutriMessageDetails.temp_scale_id
LEFT JOIN Scale humidity_scale
ON humidity_scale.id = EnutriMessageDetails.humidity_scale_id
LEFT JOIN ProcessPhase
ON ProcessPhase.id = EnutriMessageDetails.process_phase_id
LEFT JOIN ProcessPhaseAction
ON ProcessPhaseAction.id = EnutriMessageDetails.process_phase_action_id
LEFT JOIN Urgency
ON Urgency.id = EnutriMessageDetails.urgency_id
LEFT JOIN MeteoWarningDescription
ON MeteoWarningDescription.id = EnutriMessageDetails.meteo_warning_description_id
LEFT JOIN Institution cleared_by_institution
ON cleared_by_institution.id = EnutriMessageDetails.cleared_by_id
LEFT JOIN UserType
ON UserType.id = EnutriMessageDetails.user_type_id
LEFT JOIN Institution provider
ON provider.id = EnutriMessageDetails.provided_by_id
LEFT JOIN ValueAddition
ON ValueAddition.id = EnutriMessageDetails.value_addition_id

WHERE Localization.id = 2

AND 
    (EnutriMessageDetails.crop_id = 2 OR EnutriMessageDetails.crop_id IS NULL)
AND
    (EnutriMessageDetails.process_phase_id = 2 OR EnutriMessageDetails.process_phase_id IS NULL)
AND
    (EnutriMessageDetails.process_phase_action_id = 2 OR EnutriMessageDetails.process_phase_action_id IS NULL)
AND
    (EnutriMessageDetails.user_type_id = 2 OR EnutriMessageDetails.user_type_id IS NULL)
AND
    (EnutriMessageDetails.urgency_id = 2 OR EnutriMessageDetails.urgency_id IS NULL)
AND
    (EnutriMessageDetails.provided_by_id = 2 OR EnutriMessageDetails.provided_by_id IS NULL)
AND
    (EnutriMessageDetails.cleared_by_id = 2 OR EnutriMessageDetails.cleared_by_id IS NULL)

ORDER BY EnutrifoodMessage.id

Answer 1

您可以使用:

AND (Crop.id = 2 OR Crop.id IS NULL) AND ...

如果 Crop.id 为 NULL，则括号中的表达式等于 1 (true)。