MySQL GROUP_CONCAT与COALESCE有关NULL值 [英] MySQL GROUP_CONCAT vs. COALESCE concerning NULL values

问题描述

我刚刚注意到，在服务器中，列table3.note的值是NULL，而在我的本地计算机上，它们是空字符串.经过这个令人尴尬的发现之后，我进行了一些测试，并且两个平台上的所有功能均相同.

I just noticed that in the server the column table3.note values are NULL and on my local machine they are empty strings. After this embarassing discovery I made some testing and everything works the same on both platforms.

如果我有两个单元格，而第二个单元格包含实际值(第一个是NULL)，这就是它们产生的结果:

And this is what they produce if I have two cells and the second one contains an actual value (the first is NULL):

//1st
GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `table3_note`
//var_dump(): array(2) { [0]=> string(0) "" [1]=> string(4) "Test" } 

//2nd
GROUP_CONCAT(`table3`.`note`) SEPARATOR ';') AS `table3_note`
//var_dump(): array(1) { [0]=> string(4) "Test" }

因此，第一个查询(COALESCE)以空字符串的形式检索NULL s，第二个查询从结果集中剥离所有NULL值. (这是不可接受的，因为我有很多数组，它们需要同步.)

So the 1st query (COALESCE) retrieves NULLs as empty strings and the 2nd strips all NULL values from the result set. (This is unacceptable because I have many arrays and they need to be synchronized.)

由于我的错误，原来的问题已解决.我仍然想知道为什么GROUP_CONCAT即使选中也忽略NULL s.

The original problem is solved because of my mistake. I would still like to know why GROUP_CONCAT ignores NULLs even if checked.

这是正常运行的查询(不会剥离NULL s):

Here's the query that works properly (doesn't strip the NULLs):

SELECT `table1`.*
  GROUP_CONCAT(COALESCE(`table3`.`id`, '') SEPARATOR ';') AS `t3_id`,
  GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `t3_note`,
  FROM `table1`
    LEFT JOIN `table3` ON `table3`.`id` = `table1`.`id`
      GROUP BY `table1`.`id`

那么为什么这个人忽略NULL? (更多的忽略NULL值的查询选项位于原始问题部分.)

So why this one ignores NULLs? (More query option that ignore NULL values are on the original question section.)

SELECT `table1`.*
  GROUP_CONCAT(`table3`.`id` SEPARATOR ';') AS `t3_id`,
  GROUP_CONCAT(`table3`.`note` SEPARATOR ';') AS `t3_note`,
  FROM `table1`
    LEFT JOIN `table3` ON `table3`.`id` = `table1`.`id`
      GROUP BY `table1`.`id`

原始问题(不重要，我的问题...)

查询的一部分使用三个表(1:n关系，我将table2和table3的多行映射到单个table1行).提取单个单元格值的两种选择:

Original question (not important, my bad...)

A part of my query that uses three tables (1:n relationship, I'm mapping multiple rows from table2 and table3 to a single table1 row). Two alternatives for fetching a single cell value:

 //1st
 GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `table3_note`

 //2nd
 GROUP_CONCAT(`table3`.`note`) SEPARATOR ';') AS `table3_note`

在本地计算机上都可以正常工作，但是在服务器上只能是第一个.在本地计算机上，使用1st或2nd选项(使用var_dump())时，我会得到正确数量的空数组值.在服务器上，如果任何table3_note上都没有值(有很多table3_id和查询中未显示的其他字段)，则第二个选项仅返回一个空数组.

Both work fine on local machine but only the first one on the server. On my local machine I get a correct amount of empty array values when using the 1st or 2nd option (using var_dump()). On the server the 2nd option returns only an empty array if there are no values on any table3_note (there are many table3_ids and other fields not showed in the query).

所以问题是为什么?如果手册中没有非null值，则两个函数都声明返回NULL.

So the question is why? Both functions claim to return NULL if there are no non-null values according the manual.

• http://dev.mysql.com /doc/refman/5.0/en/comparison-operators.html#function_coalesce
• http://dev .mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

• http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
• http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

以下信息是不恰当的，还是我在手册中缺少某些内容?

Is the following information relevevant or am I missing something from the manual?

• 本地计算机:MySQL客户端API版本5.1.44
• 服务器:MySQL客户端API版本5.0.51a

答案是否如此简单，以至于服务器像本地计算机一样处理COALESCE函数，但是由于不匹配的MySQL客户端API版本，GROUP_CONCAT函数的处理方式有所不同?

Is the answer so simple that the server handles the COALESCE function like my local machine, but the GROUP_CONCAT function is handled differently because of the non matching MySQL Client API versions?

我现在有一个可行的解决方案，因此从某种意义上说这不是一个真正的问题，我需要解决此问题.我只想知道为什么会这样.并且像我正在使用的那样使用COALESCE有什么陷阱吗?使用for循环打印数组时，是否存在数组未正确同步的危险? (至少快速测试没有发现任何问题.)

I now have a working solution so this isn't a real question in a sense that I need to fix this. I'd just like to know why this is as it is. And are there any pitfalls in using COALESCE like I'm using? Is there a danger that arrays are not properly synchronized when printing them using a for loop? (At least a quick testing didn't reveal any problems.)

最后的笔记.我尝试使用这些方法和其他方法(IFNULL，IS NULL等)，例如在以下问题中建议使用的方法:

Final notes. I tried using these and some other methods (IFNULL, IS NULL etc.) like suggested for example in these questions:

• 如果任何值为NULL，则GROUP_CONCAT返回NULL
• 未返回group_concat具有空值的行

• GROUP_CONCAT return NULL if any value is NULL
• Rows with null value for group_concat not returned

但是结果是一样的:可以在本地计算机上运行，​​但不能在服务器上运行.以下查询:

But the result was the same: works on a local machine but not on the server. Queries below:

//another option for the query
IF(SUM(`table3`.`note` IS NULL) = 0, GROUP_CONCAT(`table3`.`note` SEPARATOR ';'), NULL) AS `table3_note`

//and another one...
ISNULL(GROUP_CONCAT(`table3`.`note` SEPARATOR ';'), '') AS `table3_note`

MySQL手册也说:

除非另有说明，否则组函数将忽略NULL值.

Unless otherwise stated, group functions ignore NULL values.

这是否意味着即使已选中，COALESCE也不会像GROUP_CONCAT那样忽略NULL值?这仍然不能解释服务器和本地计算机的不同行为.还是呢?

Does this mean COALESCE doesn't ignore NULL values like GROUP_CONCAT does, even if checked? This still doesn't explain the different behaviours of the server and local machine. Or does it?

推荐答案

这是正常运行的查询(不剥离NULL s):

Here's the query that works properly (doesn't strip the NULLs):

SELECT `table1`.*
  GROUP_CONCAT(COALESCE(`table3`.`id`, '') SEPARATOR ';') AS `t3_id`,
  GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `t3_note`,
  FROM `table1`
    LEFT JOIN `table3` ON `table3`.`id` = `table1`.`id`
      GROUP BY `table1`.`id`

原始问题保留不变.相当混乱且难以理解，但上面的查询对我有用.

Original question is left intact. It's quite messy and difficult to understand but the query above works for me.

这篇关于MySQL GROUP_CONCAT与COALESCE有关NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！