MySQL创建视图用CONCAT拼接字段遇NULL值导致数据丢失问题

问题根源 #

MySQL原生CONCAT函数的默认逻辑为：只要传入的任意一个参数为NULL，整个函数的返回结果就为NULL，因此当contacts表中firstname或lastname字段存在NULL值时，拼接出的fullname字段会直接返回NULL，造成数据缺失。

推荐解决方案 #

使用CONCAT_WS()函数替代原生CONCAT完成字符串拼接：

• 该函数第一个参数为统一的拼接分隔符，会自动跳过值为NULL的字段，不会因为单个字段为空导致整个拼接结果为NULL
• 拼接时不会产生多余的前置、后置空格
• 后续如果需要将prefix（称谓前缀）、suffix（名讳后缀）加入全名拼接，直接在函数参数中追加对应字段即可，扩展性更强
• 即使firstname和lastname同时为NULL，函数也会返回空字符串而非NULL，完全满足数据不丢失的要求

调整后的视图创建SQL语句如下：

CREATE VIEW view_contacts
AS
SELECT
contacts.PRIMARYKEY AS CONTACTPK,
CONCAT_WS(' ', contacts.FIRSTNAME, contacts.LASTNAME) AS FULLNAME,
contacts.EMAIL,
company.NAME AS ORGANISATION
FROM contacts
LEFT JOIN company ON contacts.COMPANYFK = company.PRIMARYKEY
ORDER BY contacts.LASTNAME ASC;

旧版本兼容方案 #

如果使用的是不支持CONCAT_WS的极旧版本MySQL，可以通过IFNULL()函数提前将可能为NULL的字段转换为空字符串，再传入CONCAT拼接：

CREATE VIEW view_contacts
AS
SELECT
contacts.PRIMARYKEY AS CONTACTPK,
TRIM(CONCAT(
  IFNULL(contacts.FIRSTNAME, ''),
  ' ',
  IFNULL(contacts.LASTNAME, '')
)) AS FULLNAME,
contacts.EMAIL,
company.NAME AS ORGANISATION
FROM contacts
LEFT JOIN company ON contacts.COMPANYFK = company.PRIMARYKEY
ORDER BY contacts.LASTNAME ASC;

注意：这种写法需要在拼接结果外层套TRIM()函数，避免某一个字段为NULL时出现多余的前置/后置空格，整体写法冗余且扩展性差，无兼容需求时优先选择CONCAT_WS方案。

内容的提问来源于stack exchange，提问作者JCorden