MySQL:存储过程未显示正确的结果

Question

今天我开始尝试存储过程中的 If Else 语句。

这是我的查询:

BEGIN
    SELECT p.id,
        p.content,
        p.title,
        t.id as type_id,
        t.name as type_name,
        c.id as category_id,
        c.name as category_name,
        s.id as status_id,
        s.name as status_name,
        u.id as user_id,
        u.name as author,
        u.username as username
    FROM posts p
    JOIN post_types t on t.id = p.type_id
    JOIN categories c ON c.id = p.category_id
    JOIN statuses s ON s.id = p.status_id
    JOIN users u ON u.id = p.user_id
    WHERE p.deleted_at IS NULL 
        AND p.user_id = user 
        AND p.type_id IN (IF(type='all', (SELECT id FROM post_types),(SELECT id FROM post_types WHERE name = type))) 
        AND p.created_at BETWEEN start_date and end_date
    ORDER BY p.id DESC
    LIMIT per_page
    OFFSET skip;
END

在该查询中，我尝试选择并联接某个表，但问题出在Where语句AND p.type_id IN (IF(type='all', (SELECT id FROM post_types),(SELECT id FROM post_types WHERE name = type)))

当我将类型参数作为“all”传递时，存储过程返回零值，但是当我将类型参数作为“content”或“photo”传递时，它返回正确的值。IN 语句似乎无法读取我的 SELECT id FROM post_types 查询

任何人都可以纠正或给我引用如何做到这一点？

我是不是写错了什么？感谢您的任何建议!

Answer 1

使用流量控制函数，如 CASE()或IF()当查询返回多于一行时，您将收到错误:

mysql> DROP TABLE IF EXISTS `post_types`, `posts`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `posts` (
    ->   `id` SERIAL,
    ->   `type_id` BIGINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `post_types` (
    ->   `id` SERIAL,
    ->   `type` VARCHAR(7) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `post_types`
    ->   (`type`)
    -> VALUES
    ->   ('content'),
    ->   ('photo');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `posts`
    ->   (`type_id`)
    -> VALUES
    ->   (1),
    ->   (2),
    ->   (2),
    ->   (1),
    ->   (2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `id`,
    ->   `type_id`
    -> FROM
    ->   `posts`;
+----+---------+
| id | type_id |
+----+---------+
|  1 |       1 |
|  2 |       2 |
|  3 |       2 |
|  4 |       1 |
|  5 |       2 |
+----+---------+
5 rows in set (0.00 sec)

mysql> SELECT
    ->   `id`,
    ->   `type`
    -> FROM
    ->   `post_types`;
+----+---------+
| id | type    |
+----+---------+
|  1 | content |
|  2 | photo   |
+----+---------+
2 rows in set (0.00 sec)

mysql> SET @`type` := 'all';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   `id`,
    ->   `type_id`
    -> FROM
    ->   `posts`
    -> WHERE `type_id` IN (
    ->   IF(@`type` = 'all',
    ->       (SELECT `id`
    ->        FROM `post_types`),
    ->       (SELECT `id`
    ->        FROM `post_types`
    ->        WHERE `type` = @`type`)
    ->     )
    -> );
ERROR 1242 (21000): Subquery returns more than 1 row

参见13.2.10.5 Row Subqueries和 db-fiddle .

一个可能的解决方法是重写查询:

mysql> SET @`type` := 'all';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   `id`,
    ->   `type_id`
    -> FROM
    ->   `posts`
    -> WHERE `type_id` IN (
    ->   SELECT
    ->     `id`
    ->   FROM
    ->     `post_types`
    ->   WHERE
    ->     `type` = @`type` OR
    ->     'all' = @`type`
    -> );
+----+---------+
| id | type_id |
+----+---------+
|  1 |       1 |
|  2 |       2 |
|  3 |       2 |
|  4 |       1 |
|  5 |       2 |
+----+---------+
5 rows in set (0.00 sec)

mysql> SET @`type` := 'photo';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   `id`,
    ->   `type_id`
    -> FROM
    ->   `posts`
    -> WHERE `type_id` IN (
    ->   SELECT
    ->     `id`
    ->   FROM
    ->     `post_types`
    ->   WHERE
    ->     `type` = @`type` OR
    ->     'all' = @`type`
    -> );
+----+---------+
| id | type_id |
+----+---------+
|  2 |       2 |
|  3 |       2 |
|  5 |       2 |
+----+---------+
3 rows in set (0.01 sec)

参见db-fiddle .