如果任何字段包含 NULL，则 MySQL CONCAT 返回 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15741314/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL CONCAT returns NULL if any field contain NULL
提问by neeraj
I have following data in my table "devices"
我的表“设备”中有以下数据
affiliate_name affiliate_location model ip os_type os_version
cs1 inter Dell 10.125.103.25 Linux Fedora
cs2 inter Dell 10.125.103.26 Linux Fedora
cs3 inter Dell 10.125.103.27 NULL NULL
cs4 inter Dell 10.125.103.28 NULL NULL
I executed below query
我执行了以下查询
SELECT CONCAT(`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`) AS device_name
FROM devices
It returns result given below
它返回下面给出的结果
cs1-Dell-10.125.103.25-Linux-Fedora
cs2-Dell-10.125.103.26-Linux-Fedora
(NULL)
(NULL)
How to come out of this so that it should ignore NULL AND result should be
如何解决这个问题，以便它应该忽略 NULL 并且结果应该是
cs1-Dell-10.125.103.25-Linux-Fedora
cs2-Dell-10.125.103.26-Linux-Fedora
cs3-Dell-10.125.103.27-
cs4-Dell-10.125.103.28-
回答by John Woo
convert the NULLvalues with empty string by wrapping it in COALESCE
NULL通过将其包装在空字符串中来转换值COALESCE
SELECT CONCAT(COALESCE(`affiliate_name`,''),'-',COALESCE(`model`,''),'-',COALESCE(`ip`,''),'-',COALESCE(`os_type`,''),'-',COALESCE(`os_version`,'')) AS device_name
FROM devices
回答by Gurmeet
回答by patrick
To have the same flexibility in CONCAT_WS as in CONCAT (if you don't want the same separator between every member for instance) use the following:
要在 CONCAT_WS 中具有与 CONCAT 中相同的灵活性（例如，如果您不想在每个成员之间使用相同的分隔符），请使用以下内容：
SELECT CONCAT_WS("",affiliate_name,':',model,'-',ip,... etc)
回答by Harshil
SELECT CONCAT(isnull(`affiliate_name`,''),'-',isnull(`model`,''),'-',isnull(`ip`,''),'-',isnull(`os_type`,''),'-',isnull(`os_version`,'')) AS device_name
FROM devices
回答by Ken4Edge
CONCAT_WSstill produces null for me if the first field is Null. I solved this by adding a zero length string at the beginning as in
CONCAT_WS如果第一个字段为 Null，仍然为我生成 null。我通过在开头添加一个零长度字符串来解决这个问题，如
CONCAT_WS("",`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`)
however
然而
CONCAT("",`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`)
produces Null when the first field is Null.
当第一个字段为 Null 时产生 Null。
回答by Dinesh Rabara
you can use if statement like below
您可以使用如下 if 语句
select CONCAT(if(affiliate_name is null ,'',affiliate_name),'- ',if(model is null ,'',affiliate_name)) as model from devices
