MySQL 的“IF EXISTS"的使用

2021-11-20 00:00:00 mysql

这里有两个我想使用但返回错误消息的语句:

Here are two statements that I'd like to work, but which return error messages:

IF EXISTS (SELECT * FROM gdata_calendars WHERE `group` =  ? AND id = ?) SELECT 1 ELSE SELECT 0

和

IF ((SELECT COUNT(*) FROM gdata_calendars WHERE `group` =  ? AND id = ?) > 0)  SELECT 1 ELSE SELECT 0;

问号在那里是因为我在 PHP 的 PDO 中使用了参数化的、准备好的语句.但是，我也尝试过手动使用数据执行此操作，但确实不起作用.

The question marks are there because I use parametrized, prepared, statements with PHP's PDO. However, I have also tried executing this with data manually, and it really does not work.

虽然我想知道为什么它们每个都不起作用，但如果可以使其起作用，我更愿意使用第一个查询.

While I'd like to know why each of them doesn't work, I would prefer to use the first query if it can be made to work.

推荐答案

不能在函数的 OUTSIDE 使用 IF 控制块.所以这会影响您的两个查询.

You cannot use IF control block OUTSIDE of functions. So that affects both of your queries.

将 EXISTS 子句转换为子查询，而不是在 IF 函数中

Turn the EXISTS clause into a subquery instead within an IF function

SELECT IF( EXISTS(
             SELECT *
             FROM gdata_calendars
             WHERE `group` =  ? AND id = ?), 1, 0)

实际上，布尔值返回为 1 或 0

In fact, booleans are returned as 1 or 0

SELECT EXISTS(
         SELECT *
         FROM gdata_calendars
         WHERE `group` =  ? AND id = ?)

相关文章