时间戳

首页 > 解决方案 > Mariadb longtext union 总是返回空字符串

mariadb - Mariadb longtext union 总是返回空字符串

问题描述

表结构

| did (`int(11)`) | data (longtext) |
-------------------------------------
|        1        | `<h1>It Works</h1>` <-- encrypted |

.

.

.

我的查询

SET @_did = 1;
SET @_key = 'test';
    SELECT
        COUNT(*) AS cnt,
        -1 AS did,
        '' AS `data`
    FROM
        GROUP_DESIGN
    WHERE
            (               
                    (@_did IS NOT NULL AND did = @_did)
                OR  (@_did IS NULL)
            )
    UNION ALL
    SELECT
        D_S.cnt,
        D_S.did,
        D_S.data,
    FROM
        (
            SELECT
                NULL AS cnt,
                did,
                CAST(AES_DECRYPT(UNHEX(`data`), SHA2(@_key,256)) AS CHAR) AS `data`
            FROM
                GROUP_DESIGN G_D
            WHERE
                    (               
                            (@_did IS NOT NULL AND did = @_did)
                        OR  (@_did IS NULL)
                    )
            ORDER BY
                did DESC
        ) D_S;

预期行为

显示datacol 数据。

| cnt | did | data|
-------------------
|  1  |  -1 | '' |
| null| 1   | <h1>It works!</h1> |

实际行为

| cnt | did | data|
-------------------
|  1  |  -1 | '' |
| null| 1   | '' |

datacol 总是返回空字符串，如 ''。我检查的不是解密失败。

如果删除之前的计数查询UNION ALL；

SELECT
        COUNT(*) AS cnt,
        -1 AS did,
        '' AS `data`,
    FROM
        GROUP_DESIGN
    WHERE
            (               
                    (@_did IS NOT NULL AND did = @_did)
                OR  (@_did IS NULL)
            )
    UNION ALL

它返回datacol ok。但是使用 count 查询datacol 总是返回空字符串。

环境

标签： mariadb

解决方案

至于NULL测试...

                (@_did IS NOT NULL AND did = @_did)
            OR  (@_did IS NULL)

由于= NULL总是失败，可以简化为

                (did = @_did)

至于

UNHEX(`data`)

假设data是十六进制。也许您打算使用HEX()? 为什么要费心去做呢？

至于

SHA2(@_key,256)

为什么不简单地使用@_key？我不认为通过破坏钥匙可以获得任何东西。

推荐阅读