php - 如何在mysql存储过程中使用条件

Question

我想编写一个 MySQL 存储过程并提供 4 个变量 _keyword、_catid、_low 和 high，然后如果 _catid 不为空，则过滤结果，其中 category_id 列等于 _catid，如果它 (_catid) 为空，则不执行类似这样的操作:

CREATE DEFINER = `root`@`localhost` PROCEDURE `sp_company_search` (
    IN `_catid` INT,
    IN `_keyword` VARCHAR (200) CHARSET utf8,
    IN `_low` INT,
    IN `_high` INT
) NO SQL SELECT
    i.images_id,
    c. NAME,
    c.address,
    c.id,
    cat.title,
    c.extra
FROM
    company c
LEFT JOIN category cat ON c.category_id = cat.id
LEFT JOIN company_images i ON c.id = i.company_id
WHERE
    (
        c. NAME LIKE _keyword
        OR c.extra LIKE _keyword
    ) 

    -- what i need TO DO: 
    -- IF (_catid != '') THEN { put "and c.category_id=_catid" IN QUERY }

    ORDER BY
        c.id DESC
    LIMIT _low, _high

Answer 1

您可以使用 or 逻辑运算符模拟此行为:

SELECT
    i.images_id,
    c. NAME,
    c.address,
    c.id,
    cat.title,
    c.extra
FROM
    company c
LEFT JOIN category cat ON c.category_id = cat.id
LEFT JOIN company_images i ON c.id = i.company_id
WHERE
    (
        c. NAME LIKE _keyword
        OR c.extra LIKE _keyword
    ) AND
    (_catid = '' OR c.category_id=_catid)
    ORDER BY
        c.id DESC
    LIMIT _low, _high