MySQL 返回一个空字段：CONCAT(nonEmpty1,empty2,nonEmpty3) = NULL

MySQL returning an empty field: CONCAT(nonEmpty1,empty2,nonEmpty3) = NULL

提问by Piskvor left the building

I have PHP 5 code accessing a MyISAM table on MySQL 5 server. The query looks like this:

我有 PHP 5 代码访问 MySQL 5 服务器上的 MyISAM 表。查询如下所示：

SELECT CONCAT(fName1,' ',mName2,' ',lName3) AS userName 
    FROM users 
    WHERE level > 10

When there's no mName filled in, I am expecting output like "fname lname" , but I'm getting "" (empty string) instead(the number of rows returned is correct). Where am I making a mistake?

当没有填写 mName 时，我期待像 "fname lname" 这样的输出，但我得到的是 "" （空字符串）（返回的行数是正确的）。我在哪里犯了错误？

PHP code:

PHP代码：

<?php
$result = mysql_query($the_above_query);
while ($result_row = mysql_fetch_assoc($result)) {
    // do stuff with the name
    // except I'm getting empty strings in $result_row['userName']
}

Relevant part of table structure:

表结构的相关部分：

CREATE TABLE users {
    /* -snip- */ 
    `fName1` varchar(50) default NULL,      
    `mName2` varchar(50) default NULL,      
    `lName3` varchar(50) default NULL,      
    `level` int(11) default 0,      
    /* -snip- */ 
} ENGINE=MyISAM DEFAULT CHARSET=utf8;

(also, is this way (column concatenation in MySQL) a good idea, or should I fetch the columns to PHP and join them there?)

（另外，这种方式（MySQL 中的列连接）是一个好主意，还是我应该将列提取到 PHP 并在那里加入它们？）

---

---

Turns out that I was getting back a NULL; PHP treats a returned NULL and empty string("") similarly, you'd have to compare with === to see the difference.

结果我得到了一个 NULL；PHP 类似地处理返回的 NULL 和空字符串（“”），您必须与 === 进行比较才能看到差异。

回答by chocojosh

From google: http://bugs.mysql.com/bug.php?id=480

来自谷歌：http: //bugs.mysql.com/bug.php?id=480

[23 May 2003 4:32] Alexander Keremidarski

[2003 年 5 月 23 日 4:32] 亚历山大·克雷米达尔斯基

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php

感谢您抽出时间给我们写信，但这不是错误。请仔细检查http://www.mysql.com/documentation/上提供的文档 以及http://bugs.mysql.com/how-to-report.php上有关如何报告错误的说明

This is doccumented behaviour of CONCAT() function.

这是 CONCAT() 函数的文档化行为。

From Manual chapter 6.3.2 String Functions

来自手册章节 6.3.2 字符串函数

CONCAT(str1,str2,...) Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL

CONCAT(str1,str2,...) 返回连接参数产生的字符串。如果任何参数为 NULL，则返回 NULL

Use CONCAT_WS() instead or wrap NULLable paremeters with IFNULL() function.

改用 CONCAT_WS() 或使用 IFNULL() 函数包装可空参数。

Documentation and usage for CONCAT_WS: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

CONCAT_WS 的文档和用法：http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

回答by ErsatzRyan

From MYSQL docs

来自 MYSQL 文档

CONCAT() returns NULL if any argument is NULL.

如果任何参数为 NULL，则 CONCAT() 返回 NULL。

you want to use CONCAT_WS()

你想使用 CONCAT_WS()

CONCAT_WS(separator,str1,str2,...)

But best bet is to just pull it back and use php cause if you need a different format or just one of those fields later you'll have to make another db call

但最好的办法是将它拉回来并使用 php 因为如果您需要不同的格式或只是其中一个字段，那么您将不得不进行另一个 db 调用

回答by Keeper

In MySQL concatenating any string to a NULL value results in NULL. You have to check for NULL before concatenate using IFNULL:

在 MySQL 中，将任何字符串连接到 NULL 值都会导致 NULL。您必须在使用 IFNULL 连接之前检查 NULL：

SELECT CONCAT(IFNULL(fName1,''),' ',IFNULL(mName2,''),' ',IFNULL(lName3,'')) AS userName 
FROM users 
WHERE level > 10

回答by ImaginedDesign

This was the solution I came up with which included Keeper and Ersatz answer. System would not allow me to vote you guys up though :(

这是我想出的解决方案，其中包括 Keeper 和 Ersatz 的答案。系统不允许我投票给你们：（

CONCAT_WS(IFNULL(ts_usr_nameDetails.first_name,''),' ',IFNULL(ts_usr_lib_connectionNameDetails.first_name,'')) AS composerName

CONCAT_WS(IFNULL(ts_usr_nameDetails.first_name,''),' ',IFNULL(ts_usr_lib_connectionNameDetails.first_name,'')) AS composerName

This allowed for some amazing results

这允许了一些惊人的结果

回答by Jazzy

This is an answer based on the solution above by @chocojosh and another question here: MySQL/SQL: Update with correlated subquery from the updated table itself

这是基于@chocojosh 上面的解决方案和这里的另一个问题的答案：MySQL/SQL: Update withrelated subquery from the updated table

I had a similar problem, but I was trying to concat a bunch of group_concats and some were NULL which caused the whole row to be NULL. The goal was to place data from other tables into a single field in the main table to allow fulltext searches.

我有一个类似的问题，但我试图连接一堆 group_concats，有些是 NULL，这导致整行都是 NULL。目标是将其他表中的数据放入主表中的单个字段中，以便进行全文搜索。

Here is the SQL that worked. Note the first parameter for concat_ws I made ' ', this allowed for spaces between the values for the fulltext field.

这是有效的 SQL。注意 concat_ws 的第一个参数我做了 ' '，这允许全文字段的值之间有空格。

Hope this helps someone.

希望这可以帮助某人。

update
products target
INNER JOIN 
(
    select p.id, 
    CONCAT_WS(
    ' ',
        (select GROUP_CONCAT(field SEPARATOR ' ') from table1 where productId = p.id),
        p.title,' ', 
        (select GROUP_CONCAT(field, ' ', descriptions SEPARATOR ' ') from table2 where productId = p.id),
        (select GROUP_CONCAT(field SEPARATOR ' ') from table3 where productId = p.id),
        (select GROUP_CONCAT(field, ' ', catno SEPARATOR ' ') from table4 where productId = p.id),
        (select GROUP_CONCAT(field SEPARATOR ' ') from table5 where productId = p.id),
        (select GROUP_CONCAT(field SEPARATOR ' ') from table6 where productId = p.id),
        (select GROUP_CONCAT(field SEPARATOR ' ') from table7 where productId = p.id)
    ) as ft
    from products p
) as source
on target.id = source.id
set target.fulltextsearch = source.ft

回答by amaster

you could also the COALESCE()function to return the first non-null value. Like so:

您还可以使用COALESCE()函数返回第一个非空值。像这样：

SELECT CONCAT(fName1,COALESCE(CONCAT(' ',mName2,' '),' '),lName3) AS userName 
  FROM users 
  WHERE level > 10

This would also only put one space if there was no middle name and a space before and after if there was a middle name.

如果没有中间名，这也只会放一个空格，如果有中间名，则前后各有一个空格。

Reference for this function can be found at: 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