【已解决】处理MySQL使用concat()函数时遇到null值的问题

问题描述

使用CONCAT()拼接结果是，当CONCAT()函数中的一个参数为null，那么不管其他字符串是否有值，最后返回的拼接结果总是null，如下所示：

SELECT
	name,
	address,
	nationality,
	CONCAT('my name is ', name, ', to live in ', address, ', and i am from ', nationality) as str
FROM `user2`

MySQL 官方文档有句话

解决办法

1. 使用 COALESCE() 函数转换null值

SELECT
	name,
	address,
	nationality,
	CONCAT('my name is ', COALESCE(name, ''), ', to live in ', COALESCE(address, ''), ', and i am from ', COALESCE(nationality, '')) as str
FROM `user2`

2. 使用IFNULL() 函数转换null值

SELECT
	name,
	address,
	nationality,
	CONCAT('my name is ', ifnull(name, ''), ', to live in ', ifnull(address, ''), ', and i am from ', ifnull(nationality, '')) as str
FROM `user2`

3. 尝试使用CONCAT_WS() 函数拼接字符串

SELECT
	name,
	address,
	nationality,
	CONCAT_WS(',',name,address,nationality) as str
FROM `user2`

参考资料：

1. stackoverflow.com/questions/1…
2. 12.8 String Functions and Operators
3. 12.5 Flow Control Functions