如果任何字段包含 NULL，则 MySQL CONCAT 返回 NULL [英] MySQL CONCAT returns NULL if any field contain NULL

问题描述

我的表设备"中有以下数据

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    

我执行了以下查询

SELECT CONCAT(`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`) AS device_name
FROM devices

它返回下面给出的结果

cs1-Dell-10.125.103.25-Linux-Fedora
cs2-Dell-10.125.103.26-Linux-Fedora
(NULL)
(NULL)

如何解决这个问题，以便它应该忽略 NULL 并且结果应该是

How to come out of this so that it should ignore NULL AND result should be

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-

推荐答案

通过将 NULL 值包装在 COALESCE

SELECT CONCAT(COALESCE(`affiliate_name`,''),'-',COALESCE(`model`,''),'-',COALESCE(`ip`,''),'-',COALESCE(`os_type`,''),'-',COALESCE(`os_version`,'')) AS device_name
FROM devices

这篇关于如果任何字段包含 NULL，则 MySQL CONCAT 返回 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！