如何简化CV面试状态表的MySQL查询语句且保持结果一致？

我有一张cv.cv_interview_status表，表内相关tinyint类型字段的取值可为null、0（不通过）、1（通过）、3（不适用）。需要筛选出符合严格顺序的null和值为1的记录，但当前使用的SELECT查询语句过于冗长，请求对该查询进行重构简化，确保查询结果保持不变。

建表语句 #

create table cv.cv_interview_status
(
    id                    int auto_increment
        primary key,
    type                  varchar(255)                       not null,
    date                  varchar(255)                       not null,
    issue                 varchar(255)                       not null,
    recruiter             varchar(255)                       not null,
    department            varchar(255)                       not null,
    position              varchar(255)                       not null,
    feedback_department   tinyint                            null,
    contacted_hr          tinyint                            null,
    office_interview      tinyint                            null,
    department_testing    tinyint                            null,
    department_acceptance tinyint                            null,
    add_cv                tinyint                            null,
    psychological_test    tinyint                            null,
    department_interview  tinyint                            null,
    accepted_not_accepted tinyint                            null,
    salary_request_to_ceo tinyint                            null,
    ceo_feedback          tinyint                            null,
    salary_offer          tinyint                            null,
    accept_salary_offer   tinyint                            null,
    hr_documentation      tinyint                            null,
    hr_legal_requirements tinyint                            null,
    date_created          datetime default CURRENT_TIMESTAMP not null
);

当前冗长的查询语句 #

SELECT *
FROM cv_interview_status
WHERE 1
  AND (
        (`feedback_department` IS NULL)
        OR
        (
                `feedback_department` = 1
                AND `contacted_hr` IS NULL
                AND `office_interview` IS NULL
                AND `department_testing` IS NULL
                AND `department_acceptance` IS NULL
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` IS NULL
                AND `department_testing` IS NULL
                AND `department_acceptance` IS NULL
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` = 1
                AND `office_interview` = 1
                AND `department_testing` IS NULL
                AND `department_acceptance` IS NULL
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` IS NULL
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` = 1
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` = 1
                AND `ceo_feedback` = 1
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` = 1
                AND `ceo_feedback` = 1
                AND `salary_offer` = 1
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` = 1
                AND `ceo_feedback` = 1
                AND `salary_offer` = 1
                AND `accept_salary_offer` = 1
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
    );

该查询的筛选功能正常，但语句过于冗长，请问如何对其进行重构？

简化后的解决方案 #

简化查询语句 #

SELECT
    *
FROM
    cv_interview_status
WHERE
    ( `feedback_department` IS NULL )
   OR (
            `feedback_department` = 1
        AND (
            (      IFNULL( `contacted_hr`, 1 )
                  * IFNULL( `office_interview`, 1 )
                  * IFNULL( `department_testing`, 1 )
                  * IFNULL( `department_acceptance`, 1 )
                  * IFNULL( `add_cv`, 1 )
                  * IFNULL( `psychological_test`, 1 )
                  * IFNULL( `department_interview`, 1 )
                  * IFNULL( `accepted_not_accepted`, 1 )
                  * IFNULL( `salary_request_to_ceo`, 1 )
                  * IFNULL( `ceo_feedback`, 1 )
                  * IFNULL( `salary_offer`, 1 )
                  * IFNULL( `accept_salary_offer`, 1 )

                ) = 1 )
        AND `hr_documentation` IS NULL
        AND `hr_legal_requirements` IS NULL
    )

简化思路 #

原查询通过枚举所有前置字段全为1、后续字段全为null的组合来筛选记录，这种方式会随着字段数量增加导致语句极度冗长。

简化方案利用IFNULL函数将null值转换为1，然后将这些字段的值相乘：

• 若字段值为1或null（转1），则乘积为1
• 若字段存在0或3的取值，乘积会变为0或3，不满足等于1的条件

这样就用一个条件替代了原查询中大量的OR分支，同时完全保留了原筛选逻辑，确保结果一致。

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