处理MySQL使用concat()函数时遇到null值的问题

问题描述

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

1
2
3
4
5
6

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值

1
2
3
4
5
6

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值

   1 2 3 4 5 6

   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() 函数拼接字符串

   1 2 3 4 5 6

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

参考资料：

1. https://stackoverflow.com/questions/15741314/mysql-concat-returns-null-if-any-field-contain-null

2. 12.8 String Functions and Operators

3. 12.5 Flow Control Functions