关于空值的MySQL GROUP_CONCAT vs. COALESCE

UPDATE

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.

我刚刚注意到，在服务器的列表3中。注意值为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" }

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.)

因此，第一个查询(合并)检索NULL作为空字符串，而第2条则从结果集中删除所有NULL值(这是不可接受的，因为我有许多数组，它们需要同步)。

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

原来的问题因我的错误而得以解决。我仍然想知道为什么GROUP_CONCAT会忽略NULLs，即使选中它。

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`

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

为什么这个忽略了NULLs?(更多忽略空值的查询选项位于原始问题部分。)

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`

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:

我查询的一部分使用了三个表(1:n关系，我将表2和表3中的多个行映射到一个表1行)。获取单个单元格值的两种选择:

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

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

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).

这两种方法都可以在本地机器上正常工作，但只适用于服务器上的第一个。在我的本地机器上，当使用第1或第2个选项时，我获得了正确的空数组值(使用var_dump())。在服务器上，如果任何table3_note上都没有值，第二个选项只返回一个空数组(有许多table3_id和查询中没有显示的其他字段)。

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

问题是为什么?如果手册中没有非空值，这两个函数都声明返回NULL。

• http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
• 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/group-by-functions.html function_group-concat

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

以下信息是否相关，还是我在手册中漏掉了什么?

• Local machine: MySQL Client API version 5.1.44
• 本地机器:MySQL客户端API版本5.1.44
• Server: MySQL Client API version 5.0.51a
• 服务器:MySQL客户端API版本5.0.51a

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?

答案是否如此简单，以至于服务器像我的本地机器一样处理合并函数，但是GROUP_CONCAT函数由于不匹配的MySQL客户端API版本而处理不同?

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.)

我现在有了一个工作解决方案所以这不是一个真正的问题我需要解决这个问题。我只是想知道为什么会这样。像我这样使用合并有什么缺陷吗?在使用for循环打印数组时，是否存在数组不能正确同步的危险?(至少快速测试没有发现任何问题。)

---

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

最后指出。我尝试过使用这些方法和其他一些方法(IFNULL, IS NULL等等)，比如在这些问题中建议的:

• GROUP_CONCAT return NULL if any value is NULL
• 如果任何值为空，GROUP_CONCAT返回NULL。
• Rows with null value for group_concat not returned
• group_concat未返回值为空的行

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 manual also says:

MySQL手册还说:

Unless otherwise stated, group functions ignore NULL values.

除非另有说明，组函数忽略空值。

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?

这是否意味着即使选中，联合也不会像GROUP_CONCAT那样忽略空值?这仍然不能解释服务器和本地机器的不同行为。还是它?

1 个解决方案

#1

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.

原来的问题原封未动。这是相当混乱和难以理解的，但是上面的查询对我很有用。