递归查询意外返回一个字段的空白值-解网

问：

我有以下表格：

persons:
- personid: int not null
- personname: varchar(200) not null

parents:
- parentid: int not null
- personid: int not null

一个人可能有子项，在这种情况下，表中有行，其中每行标识父/子关系。parents

我现在有以下递归查询：

        WITH RECURSIVE cte (personid, parentid) as (
            -- Anchor query
            SELECT
                personid AS personid,
                NULL AS parentid
            FROM
                persons
            WHERE
                personid = ?
            UNION ALL
            -- Recursive query
            SELECT
                p.personid,
                p.parentid
            FROM
                parents p
            INNER JOIN
                cte ON p.parentid = cte.personid
        )
        SELECT
            parentid,
            personid
        FROM
            cte

锚点行被赋予了要开始的 ID。我希望此查询以递归方式选择该人以及他的所有孩子。person

但它不起作用：查询返回预期的行数，但是该字段始终为空（！），除了值所在的第一行（这是我想要的）。对于每一行，该列的值似乎是正确的。parentidNULLpersonid

这怎么可能值为空？我做错了什么？parentid

我正在使用MariaDB 10.5.19。MySQL 8.0.27 表现出相同的行为。

sql mysql mariadb 递归查询

评论

2赞 Bill Karwin 11/10/2023

我尝试使用 MariaDB 10.5 和 MySQL 8.0 进行测试，它似乎可以正常工作： dbfiddle.uk/WjAsmHa8 你能提供一个 dbfiddle 来演示这个问题吗？

0赞 andynewman 11/10/2023

我看到您正在使用 CAST（NULL AS UNSIGNED）而不是 NULL，它对您有用，这很棒，谢谢。我相信这就是问题所在。如果替换为 NULL，则它不起作用。

答：

0赞 Bill Karwin 11/10/2023 #1

问题似乎出在递归查询的第一部分：

        SELECT
            personid AS personid,
            NULL AS parentid
        ...

parentid 的数据类型由此决定。键入为 NULL，而不是必需的 INT。在 UNION 中，一个列可能只有一个名称和一种数据类型，这适用于 UNION 中其他查询追加的所有后续行。

当递归查询的第二部分尝试将整数放入该列时，它将失败。

当我测试在严格模式下运行的查询时，出现以下错误：

错误 1406 （22001）：第 1 行的列“parentid”的数据太长

如果在没有严格模式的情况下运行查询，则该值将被截断为某个零值。我在 MySQL 8.0 中进行了测试并得到了结果。0x

MariaDB 可能有不同的行为。我们都应该停止将MariaDB和MySQL视为兼容产品。它们具有越来越多的不相容行为。到目前为止，它们实际上是完全不同的产品。

解决方案是确保基本情况使用正确的数据类型定义该列：

        SELECT
            personid AS personid,
            CAST(NULL AS UNSIGNED) AS parentid
        ...

评论

0赞 andynewman 11/10/2023

非常感谢您的帮助。我能问为什么 CAST（NULL AS INT）不合适吗？

1赞 Bill Karwin 11/10/2023

我不知道为什么，但在 MySQL 和 MariaDB 中，或者是支持的语法。在 SQL 的其他实现中，可以对数据类型使用其他名称。这在当时一定是有道理的。CAST(<expr> AS UNSIGNED [INTEGER])CAST(<expr> AS SIGNED [INTEGER])

上一个：如何更改从 ROLLUP 创建的最后一行的文本

下一个：在 mysql 命令行中使用 mysqldump 进行备份时出错