MySQL GROUP_CONCAT和COALESCE在处理NULL值时的区别

Question

更新

我刚刚注意到在服务器上，列table3.note的值为NULL，而在我的本地机器上它们是空字符串。在做了一些测试后，我发现两个平台的结果都相同。

如果我有两个单元格，并且第二个包含实际值（第一个是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作为空字符串检索，而第二个则从结果集中剥离所有的NULL值。（这是不可接受的，因为我有很多数组需要同步。）

由于我的错误，原始问题已经解决。但我仍然想知道为什么GROUP_CONCAT即使被检查，也会忽略NULL。

下面是正确工作的查询（不会剥离NULL）：

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值的查询选项在原问题部分。）

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行。获取单个单元格值的两种替代方法：

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

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

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

所以问题是为什么？根据手册，这两个函数都声称如果没有非空值，则返回NULL。

• 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

以下信息是否相关或者我在手册中遗漏了什么？

本地机器：MySQL客户端API版本5.1.44 服务器：MySQL客户端API版本5.0.51a

答案是否如此简单，即服务器处理COALESCE函数的方式与我的本地机器相同，但由于MySQL客户端API版本不匹配，处理GROUP_CONCAT函数的方式有所不同？

我现在有一个可行的解决方案，因此这不是一个真正的问题，我只是想知道为什么会这样。使用COALESCE是否存在任何陷阱？在使用for循环打印它们时，数组是否无法正确同步？（至少快速测试没有发现任何问题。）

---

最后的注意事项。我尝试使用这些方法和其他一些方法（例如建议中提到的IFNULL，IS NULL等）：

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

但结果是相同的：在本地机器上运行正常，但在服务器上不行。以下是查询：

//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值。

这是否意味着像GROUP_CONCAT一样，即使检查过，COALESCE也不会忽略NULL值？这仍然无法解释服务器和本地机器的不同行为。还是说它可以吗？

- ZZ-bb

---

除非您揭示查询的“GROUP BY”部分，否则很难弄清楚像GROUP_CONCAT这样的摘要函数为什么可能无法按照您想要的方式工作。如果您的查询中没有“GROUP BY”，请将其放在那里。或者，理解本网页的每个单词。http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html认真阅读。 - O. Jones

请将您的解决方案移动到答案中并编辑您的问题。 - Kermit

回到SQL设计和/或mySQL开始开发的时代，我想跳过NULL更有意义。如果您使用SQL直接输出人类可读的报告，那么这是更有意义的。但是现在，它主要用于向复杂程序和OO代码提供数据，这需要更好的可预测性和精度。 - Rolf

Answer 1

这是一个能正常工作的查询（不会去掉NULL）：

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`

原始问题保持不变。虽然有点混乱和难以理解，但上面的查询对我有效。

- ZZ-bb