当前位置： 文江博客话题详情

为什么这个 MySQL 函数返回 null？

发布于 2024-07-21 12:48:25 字数 2054 浏览 10 评论 0原文

描述： 实际运行的查询返回了 4 个结果，如下所示， 我所做的只是连接项目然后返回， 但出乎意料的是，它是空的。

我认为代码是不言自明的：

DELIMITER |

DROP FUNCTION IF EXISTS get_idiscussion_ask|

CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE body varchar(600);
  DECLARE created DATETIME;
  DECLARE anonymous TINYINT(1);
  DECLARE screen_name varchar(64);
  DECLARE result TEXT;
  DECLARE cur1 CURSOR FOR SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=iask_id;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  SET result = '';
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO body, created, anonymous, screen_name;
    SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>');
  UNTIL done END REPEAT;
  CLOSE cur1;

  RETURN result;
END |

DELIMITER ;

mysql> DELIMITER ;
mysql> select get_idiscussion_ask(1);
+------------------------+
| get_idiscussion_ask(1) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.01 sec)



mysql> SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=1;
+------+---------------------+-----------+-------------+
| body | created             | anonymous | screen_name |
+------+---------------------+-----------+-------------+
| haha | 2009-05-27 04:57:51 |         0 | NULL        |
| haha | 2009-05-27 04:57:52 |         0 | NULL        |
| haha | 2009-05-27 04:57:52 |         0 | NULL        |
| haha | 2009-05-27 04:57:53 |         0 | NULL        |
+------+---------------------+-----------+-------------+
4 rows in set (0.00 sec)

对于那些认为代码不言自明的人：

为什么函数返回NULL？

原文

Description:
the query actually run have 4 results returned,as can be see from below,
what I did is just concate the items then return,
but unexpectedly,it's null.

I think the code is self-explanatory:

DELIMITER |

DROP FUNCTION IF EXISTS get_idiscussion_ask|

CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE body varchar(600);
  DECLARE created DATETIME;
  DECLARE anonymous TINYINT(1);
  DECLARE screen_name varchar(64);
  DECLARE result TEXT;
  DECLARE cur1 CURSOR FOR SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=iask_id;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  SET result = '';
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO body, created, anonymous, screen_name;
    SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>');
  UNTIL done END REPEAT;
  CLOSE cur1;

  RETURN result;
END |

DELIMITER ;

mysql> DELIMITER ;
mysql> select get_idiscussion_ask(1);
+------------------------+
| get_idiscussion_ask(1) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.01 sec)



mysql> SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=1;
+------+---------------------+-----------+-------------+
| body | created             | anonymous | screen_name |
+------+---------------------+-----------+-------------+
| haha | 2009-05-27 04:57:51 |         0 | NULL        |
| haha | 2009-05-27 04:57:52 |         0 | NULL        |
| haha | 2009-05-27 04:57:52 |         0 | NULL        |
| haha | 2009-05-27 04:57:53 |         0 | NULL        |
+------+---------------------+-----------+-------------+
4 rows in set (0.00 sec)

For those who don't think the code is self-explanatory:

Why the function returns NULL?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

姐不稀罕 2024-07-28 12:48:26

重命名您的变量和输入参数，它们是不明确的。

此查询：

SELECT  body, created, anonymous, screen_name
FROM    idiscussion
LEFT JOIN
        users
ON      idiscussion.uid = users.id
WHERE   idiscussion.iask_id = iask_id

返回您之前声明的变量（为 NULL），而不是表列。

在变量名称和输入参数名称前面添加下划线。

此外，您还对结果进行了额外的分配：

FETCH cur1 INTO body, created, anonymous, screen_name;
SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>');

在 FETCH 失败后，处理程序设置 done，但 result 仍然被分配。

将处理程序更改为：

DECLARE EXIT HANDLER FOR SQLSTATE '02000' RETURN result;

最后：在 MySQL 中，这可以通过单个查询来完成。 不需要用函数来做到这一点。

SELECT  GROUP_CONCAT(CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '')
FROM   idiscussion
LEFT JOIN
       users
ON     idiscussion.uid=users.id
WHERE  idiscussion.iask_id = @_iask_id

Rename your variables and the input parameter, they're ambiguous.

This query:

SELECT  body, created, anonymous, screen_name
FROM    idiscussion
LEFT JOIN
        users
ON      idiscussion.uid = users.id
WHERE   idiscussion.iask_id = iask_id

returns your previously declared variables (which are NULL), not the table columns.

Prepend the variable names and the input parameter name with an underscore.

Also you make an extra assignment to result:

FETCH cur1 INTO body, created, anonymous, screen_name;
SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>');

The handler sets done after the FETCH fails, but the result gets assigned nevertheless.

Change your handler to:

DECLARE EXIT HANDLER FOR SQLSTATE '02000' RETURN result;

Finally: in MySQL, this can be done with a single query. There is no need to do it with a function.

SELECT  GROUP_CONCAT(CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '')
FROM   idiscussion
LEFT JOIN
       users
ON     idiscussion.uid=users.id
WHERE  idiscussion.iask_id = @_iask_id
回复 原文
半暖夏伤 2024-07-28 12:48:26

请记住，将任何字符串与 NULL 连接起来都会返回 NULL。 尝试这个测试：

mysql> SET @s = 'test string';
mysql> SET @s = CONCAT(@s, '<tag>', NULL, '</tag>');
mysql> SELECT @s;

这将返回 NULL。

因此，当您循环游标时，如果任何行上的 body 或 created 列为 NULL，则结果 也将变为 NULL。 然后，在循环的后续迭代中，与 NULL 结果 连接的任何内容都无效； 它保持为 NULL。

尝试这样的操作：

REPEAT
    FETCH cur1 INTO body, created, anonymous, screen_name;
    SET result = CONCAT(result, 
      '<comment><body><![CDATA[', 
      COALESCE(body, ''),
      ']]></body>', 
      '<replier>', 
      IF(COALESCE(anonymous, 0) != 0, COALESCE(screen_name, ''), ''), 
      '</replier>',
      '<created>',
      COALESCE(created, ''),
      '</created></comment>'
    );
UNTIL done END REPEAT;

COALESCE() 函数是标准 SQL 中的一个有用函数。 它返回第一个非 NULL 参数。

Keep in mind that concatenating any string together with a NULL returns NULL. Try this test:

mysql> SET @s = 'test string';
mysql> SET @s = CONCAT(@s, '<tag>', NULL, '</tag>');
mysql> SELECT @s;

This returns NULL.

So as you loop through your cursor, if the body or created columns are NULL on any row, the result becomes NULL. Then on subsequent iterations of the loop anything concatenated with a NULL result has no effect; it stays NULL.

Try something like this:

REPEAT
    FETCH cur1 INTO body, created, anonymous, screen_name;
    SET result = CONCAT(result, 
      '<comment><body><![CDATA[', 
      COALESCE(body, ''),
      ']]></body>', 
      '<replier>', 
      IF(COALESCE(anonymous, 0) != 0, COALESCE(screen_name, ''), ''), 
      '</replier>',
      '<created>',
      COALESCE(created, ''),
      '</created></comment>'
    );
UNTIL done END REPEAT;

The COALESCE() function is a useful function in standard SQL. It returns its first non-NULL argument.

回复 原文
浅蓝的眸勾画不出的柔情 2024-07-28 12:48:26

CONCAT_WS（分隔符，str1，str2，...）
CONCAT_WS() 代表 CONCAT With Separator，是 CONCAT() 的特殊形式。 第一个参数是其余参数的分隔符。 分隔符添加在要连接的字符串之间：分隔符可以是字符串，其余参数也可以是字符串。 如果分隔符为 NULL，则结果为 NULL。 该函数会跳过分隔符参数后面的任何 NULL 值。

mysql>

SELECT CONCAT_WS(",","First name","Second name","Last Name");
   -> 'First name,Second name,Last Name'

mysql>

SELECT CONCAT_WS(",","First name",NULL,"Last Name");
   -> 'First name,Last Name'

在 MySQL 4.0.14 之前，CONCAT_WS() 会跳过空字符串和 NULL 值。

CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated: The separator can be a string as can the rest of the arguments. If the separator is NULL, the result is NULL. The function skips any NULL values after the separator argument.

mysql>

SELECT CONCAT_WS(",","First name","Second name","Last Name");
   -> 'First name,Second name,Last Name'

mysql>

SELECT CONCAT_WS(",","First name",NULL,"Last Name");
   -> 'First name,Last Name'

Before MySQL 4.0.14, CONCAT_WS() skips empty strings as well as NULL values.

回复 原文
So尛奶瓶 2024-07-28 12:48:26

我尝试说得很详细，因为您的问题不是；）

您期望函数的返回值是非 NULL，因为您通过仅连接非 NULL 字符串来创建返回值。

仅当其中一个字符串为 NULL 时，整个返回值才会为 NULL。 您的演示数据仅在 screen_name 中包含 NULL 值，但您尊重这种情况。

但不知何故（atm，我不知道如何）其中一个值必须为 NULL，并且要查看的相关行是带有大 CONCAT 的行。

如果出于调试原因将相关行缩短为：

SET result = if(screen_name is not null,screen_name,'')

它是否仍返回 NULL？

I try to be verbose because your question isn't ;)

You're expecting the return value of the function to be non-NULL since you're creating the return value by concatenating only non-NULL strings.

Only if one of the strings was NULL, the whole return value would be NULL. Your demo data only contains NULL values in screen_name, but you respected that case.

But somehow (atm I have no idea how) one of the values must be NULL and the relevant line to look at ist the one with the big CONCAT.

What if you shorten the relevant line for debug reasons to:

SET result = if(screen_name is not null,screen_name,'')

Does it still return NULL?

回复 原文
~没有更多了~

关于作者

玩心态

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

留蓝

文章 0 评论 0

18790681156

文章 0 评论 0

zach7772

文章 0 评论 0

Wini

文章 0 评论 0

ayeshaaroy

文章 0 评论 0

初雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文