php - 在Mysql上选择IF EXISTS THEN ELSE

Question

我尝试了很多方法来解决这个问题，检查了很多网站，尝试了更多的例子，但还没有得到我想要的。我的 php 页面上的 sql 逻辑如下所示:

IF (EXISTS (SELECT * FROM suborders WHERE subId = 1))
THEN SELECT * FROM suborders WHERE subId = 1     //if exists just do this sql again
ELSE SELECT id FROM orders WHERE id = 1 LIMIT 1;

Mysql 在第一次查询时给出语法错误。所以，我也尝试在下面使用另一种方式:

delimiter //
create procedure test()
begin
IF EXISTS (SELECT * FROM suborders WHERE subId = 1) THEN
SELECT * FROM suborders WHERE subId = 1;
ELSE
SELECT id FROM orders WHERE id = 1 LIMIT 1;
END IF;
end //
delimiter ;

call test();

使用第二个查询 mysql 返回空结果，没有错误。其实，应该是有东西要取的……

Answer 1

IF EXISTS (SELECT * FROM suborders WHERE subId = 1 )
BEGIN
   SELECT * FROM suborders WHERE subId = 1
END
ELSE
BEGIN
    SELECT id FROM orders WHERE id = 1 LIMIT 1
END

完整存储过程:

DELIMITER //
 CREATE PROCEDURE sp_select_suborders(IN `p_subId`)
    IF EXISTS (SELECT * FROM suborders WHERE subId = p_subId )
    BEGIN
       SELECT * FROM suborders WHERE subId = p_subId
    END
    ELSE
    BEGIN
        SELECT id FROM orders WHERE id = p_subId LIMIT 1
    END //
 DELIMITER ;

用法:

$stmt = $db->prepare('CALL sp_select_suborders(?)');