MySQL COALESCE 空值實(shí)戰(zhàn)詳解
簡(jiǎn)介
COALESCE 是 MySQL 里非常常用的空值處理函數(shù)。
它的作用很簡(jiǎn)單：從左到右返回第一個(gè)不是 NULL 的值。
最基礎(chǔ)示例：
SELECT COALESCE(NULL, NULL, 'hello', 'world');
結(jié)果：
+-----------------------------------------+
| COALESCE(NULL, NULL, 'hello', 'world') |
+-----------------------------------------+
| hello |
+-----------------------------------------+
因?yàn)?'hello' 是第一個(gè)非 NULL 值，后面的 'world' 不會(huì)作為結(jié)果返回。
如果所有參數(shù)都是 NULL：
SELECT COALESCE(NULL, NULL, NULL);
結(jié)果還是：NULL
一句話概括：COALESCE 負(fù)責(zé)給 NULL 找一個(gè)兜底值
它常用于這些場(chǎng)景：
- 字段為空時(shí)顯示默認(rèn)文字
- 多個(gè)聯(lián)系方式按優(yōu)先級(jí)取一個(gè)
- 聚合統(tǒng)計(jì)沒有結(jié)果時(shí)返回 0
LEFT JOIN沒匹配到數(shù)據(jù)時(shí)補(bǔ)默認(rèn)值- 拼接字符串時(shí)避免結(jié)果變成
NULL - 排序時(shí)給空值一個(gè)排序規(guī)則
- 批量更新時(shí)保留原值或設(shè)置默認(rèn)值
基本語(yǔ)法
COALESCE(value1, value2, value3, ...)
執(zhí)行規(guī)則：從左到右依次判斷，返回第一個(gè)非 NULL 的值。
示例：
SELECT COALESCE(NULL, 100, 200);
結(jié)果：
100
再看幾個(gè)例子：
SELECT COALESCE('張三', '匿名用戶');
結(jié)果：
張三
SELECT COALESCE(NULL, '匿名用戶');
結(jié)果：匿名用戶
SELECT COALESCE(NULL, NULL);
結(jié)果：
NULL
為什么需要 COALESCE？
SQL 里的 NULL 不是空字符串，也不是 0，而是“不知道、沒有值”。
很多運(yùn)算碰到 NULL 后，結(jié)果也會(huì)變成 NULL。
例如：
SELECT 100 + NULL;
結(jié)果：
NULL
字符串拼接也一樣：
SELECT CONCAT('手機(jī)號(hào)：', NULL);
結(jié)果：
NULL
這就是 NULL 最容易出問題的地方：只要參與計(jì)算或拼接，整個(gè)結(jié)果可能都沒了。
用 COALESCE 兜底后：
SELECT 100 + COALESCE(NULL, 0);
結(jié)果：
100
SELECT CONCAT('手機(jī)號(hào)：', COALESCE(NULL, '暫無(wú)'));
結(jié)果：
手機(jī)號(hào)：暫無(wú)
準(zhǔn)備一組演示數(shù)據(jù)
后面的例子直接使用這幾張表。
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
nickname VARCHAR(50),
real_name VARCHAR(50),
mobile VARCHAR(20),
backup_mobile VARCHAR(20),
email VARCHAR(100),
avatar VARCHAR(255),
department_id INT,
last_login_at DATETIME,
created_at DATETIME NOT NULL,
INDEX idx_mobile (mobile),
INDEX idx_department_id (department_id)
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
order_no VARCHAR(50) NOT NULL,
amount DECIMAL(10, 2),
discount DECIMAL(10, 2),
sort_no INT,
updated_at DATETIME,
created_at DATETIME NOT NULL,
INDEX idx_user_id (user_id),
INDEX idx_sort_no (sort_no)
);
CREATE TABLE payments (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
pay_amount DECIMAL(10, 2),
refund_amount DECIMAL(10, 2),
INDEX idx_order_id (order_id)
);
INSERT INTO departments (name) VALUES
('研發(fā)部'),
('銷售部');
INSERT INTO users
(username, nickname, real_name, mobile, backup_mobile, email, avatar, department_id, last_login_at, created_at)
VALUES
('zhangsan', '老張', '張三', '13800000001', NULL, 'zhangsan@example.com', NULL, 1, '2026-01-05 10:00:00', '2026-01-01 10:00:00'),
('lisi', NULL, '李四', NULL, '13900000002', 'lisi@example.com', '/avatar/lisi.png', 2, NULL, '2026-01-02 10:00:00'),
('wangwu', '', '王五', NULL, NULL, 'wangwu@example.com', NULL, NULL, NULL, '2026-01-03 10:00:00'),
(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-01-04 10:00:00');
INSERT INTO orders
(user_id, order_no, amount, discount, sort_no, updated_at, created_at)
VALUES
(1, 'A001', 100.00, 10.00, 2, '2026-01-06 10:00:00', '2026-01-05 10:00:00'),
(1, 'A002', 200.00, NULL, NULL, NULL, '2026-01-06 11:00:00'),
(2, 'A003', NULL, NULL, 1, '2026-01-07 10:00:00', '2026-01-07 09:00:00'),
(3, 'A004', 80.00, 5.00, NULL, NULL, '2026-01-08 10:00:00');
INSERT INTO payments (order_id, pay_amount, refund_amount) VALUES
(1, 90.00, NULL),
(2, 200.00, 20.00),
(3, NULL, NULL);
示例一：字段為空時(shí)顯示默認(rèn)值
用戶頭像為空時(shí)，顯示默認(rèn)頭像。
SELECT id, username, COALESCE(avatar, '/avatar/default.png') AS show_avatar FROM users;
結(jié)果類似：
+----+----------+---------------------+
| id | username | show_avatar |
+----+----------+---------------------+
| 1 | zhangsan | /avatar/default.png |
| 2 | lisi | /avatar/lisi.png |
| 3 | wangwu | /avatar/default.png |
| 4 | NULL | /avatar/default.png |
+----+----------+---------------------+
這類場(chǎng)景非常適合 COALESCE(字段, 默認(rèn)值)。
示例二：多個(gè)字段按優(yōu)先級(jí)取值
用戶展示名按這個(gè)順序?。?/p>
昵稱 -> 真實(shí)姓名 -> 用戶名 -> 匿名用戶
SQL：
SELECT id, COALESCE(nickname, real_name, username, '匿名用戶') AS display_name FROM users;
結(jié)果類似
+----+--------------+
| id | display_name |
+----+--------------+
| 1 | 老張 |
| 2 | 李四 |
| 3 | |
| 4 | 匿名用戶 |
+----+--------------+
第三行結(jié)果是空字符串，不是 匿名用戶。
原因是：
COALESCE 只判斷 NULL，不會(huì)把空字符串 '' 當(dāng)成 NULL。
如果空字符串也要當(dāng)成空值處理，需要配合 NULLIF。
SELECT
id,
COALESCE(
NULLIF(nickname, ''),
NULLIF(real_name, ''),
NULLIF(username, ''),
'匿名用戶'
) AS display_name
FROM users;
這時(shí)第三行會(huì)顯示 王五。
示例三：聯(lián)系方式兜底
聯(lián)系方式經(jīng)常不止一個(gè)字段，比如手機(jī)號(hào)、備用手機(jī)號(hào)、郵箱。
SELECT id, COALESCE(mobile, backup_mobile, email, '無(wú)聯(lián)系方式') AS contact FROM users;
結(jié)果類似：
+----+---------------------+
| id | contact |
+----+---------------------+
| 1 | 13800000001 |
| 2 | 13900000002 |
| 3 | wangwu@example.com |
| 4 | 無(wú)聯(lián)系方式 |
+----+---------------------+
這種寫法比多層 CASE WHEN 更短，也更容易看出優(yōu)先級(jí)。
示例四：金額計(jì)算時(shí)把 NULL 當(dāng)成 0
訂單實(shí)付金額 = 訂單金額 - 優(yōu)惠金額。
直接寫：
SELECT order_no, amount - discount AS actual_amount FROM orders;
如果 discount 是 NULL，結(jié)果也會(huì)是 NULL。
推薦寫法：
SELECT order_no, COALESCE(amount, 0) AS amount, COALESCE(discount, 0) AS discount, COALESCE(amount, 0) - COALESCE(discount, 0) AS actual_amount FROM orders;
結(jié)果類似：
+----------+--------+----------+---------------+
| order_no | amount | discount | actual_amount |
+----------+--------+----------+---------------+
| A001 | 100.00 | 10.00 | 90.00 |
| A002 | 200.00 | 0.00 | 200.00 |
| A003 | 0.00 | 0.00 | 0.00 |
| A004 | 80.00 | 5.00 | 75.00 |
+----------+--------+----------+---------------+
金額字段參與計(jì)算時(shí)，COALESCE(金額字段, 0) 很常見。
示例五：聚合函數(shù)沒有結(jié)果時(shí)返回 0
SUM、AVG 這類聚合函數(shù)，在沒有匹配數(shù)據(jù)時(shí)可能返回 NULL。
例如查詢一個(gè)不存在用戶的訂單總額：
SELECT SUM(amount) AS total_amount FROM orders WHERE user_id = 999;
結(jié)果：
NULL
使用 COALESCE 兜底：
SELECT COALESCE(SUM(amount), 0) AS total_amount FROM orders WHERE user_id = 999;
結(jié)果：
0.00
統(tǒng)計(jì)支付金額、退款金額也很常見：
SELECT COALESCE(SUM(pay_amount), 0) AS total_pay_amount, COALESCE(SUM(refund_amount), 0) AS total_refund_amount FROM payments;
注意：COUNT(*) 本身沒有匹配行時(shí)會(huì)返回 0，通常不需要寫成 COALESCE(COUNT(*), 0)。
SELECT COUNT(*) AS order_count FROM orders WHERE user_id = 999;
結(jié)果就是：
0
示例六：LEFT JOIN 后補(bǔ)默認(rèn)值
LEFT JOIN 沒匹配到右表數(shù)據(jù)時(shí)，右表字段會(huì)是 NULL。
查詢用戶和部門：
SELECT u.id, COALESCE(u.username, '未命名用戶') AS username, COALESCE(d.name, '未分配部門') AS department_name FROM users AS u LEFT JOIN departments AS d ON u.department_id = d.id;
結(jié)果類似：
+----+------------+-----------------+
| id | username | department_name |
+----+------------+-----------------+
| 1 | zhangsan | 研發(fā)部 |
| 2 | lisi | 銷售部 |
| 3 | wangwu | 未分配部門 |
| 4 | 未命名用戶 | 未分配部門 |
+----+------------+-----------------+
這類寫法適合列表頁(yè)、導(dǎo)出報(bào)表、統(tǒng)計(jì)看板。
示例七：拼接字符串時(shí)避免結(jié)果變 NULL
MySQL 的 CONCAT 只要有一個(gè)參數(shù)是 NULL，結(jié)果就會(huì)變成 NULL。
SELECT CONCAT(username, ' / ', mobile) AS user_contact FROM users;
如果 mobile 是 NULL，整段拼接結(jié)果也會(huì)是 NULL。
使用 COALESCE：
SELECT
CONCAT(
COALESCE(username, '匿名用戶'),
' / ',
COALESCE(mobile, backup_mobile, email, '無(wú)聯(lián)系方式')
) AS user_contact
FROM users;
結(jié)果類似：
+-------------------------------+
| user_contact |
+-------------------------------+
| zhangsan / 13800000001 |
| lisi / 13900000002 |
| wangwu / wangwu@example.com |
| 匿名用戶 / 無(wú)聯(lián)系方式 |
+-------------------------------+
示例八：按更新時(shí)間或創(chuàng)建時(shí)間排序
很多表都有 updated_at 和 created_at。
列表排序時(shí)，希望優(yōu)先按更新時(shí)間排序；沒有更新時(shí)間時(shí)，按創(chuàng)建時(shí)間排序。
SELECT order_no, updated_at, created_at FROM orders ORDER BY COALESCE(updated_at, created_at) DESC;
COALESCE(updated_at, created_at) 表示：
有更新時(shí)間就用更新時(shí)間，沒有更新時(shí)間就用創(chuàng)建時(shí)間。
示例九：沒有排序號(hào)的排最后
sort_no 有值的按 sort_no 排，沒有值的排最后。
SELECT order_no, sort_no FROM orders ORDER BY COALESCE(sort_no, 999999) ASC;
結(jié)果類似：
+----------+---------+
| order_no | sort_no |
+----------+---------+
| A003 | 1 |
| A001 | 2 |
| A002 | NULL |
| A004 | NULL |
+----------+---------+
這里的 999999 是一個(gè)足夠大的兜底值，讓 NULL 排到后面。
示例十：UPDATE 時(shí)保留舊值
接口更新資料時(shí)，有些字段可能沒傳。沒傳的字段通常不希望被更新成 NULL。
假設(shè)傳入的新手機(jī)號(hào)為 @new_mobile，新頭像為 @new_avatar：
SET @new_mobile = NULL; SET @new_avatar = '/avatar/new.png'; UPDATE users SET mobile = COALESCE(@new_mobile, mobile), avatar = COALESCE(@new_avatar, avatar) WHERE id = 1;
含義：
新值不是 NULL 就用新值，新值是 NULL 就保留原字段值。
這種寫法適合“部分字段更新”。不過(guò)如果業(yè)務(wù)允許把字段主動(dòng)清空為 NULL，就不能單純依賴這種寫法，需要區(qū)分“沒傳字段”和“傳了 NULL”。
COALESCE 和 IFNULL 的區(qū)別
IFNULL 是 MySQL 常用函數(shù)：
IFNULL(expr1, expr2)
含義：expr1 不是 NULL，返回 expr1；否則返回 expr2。
它只能接收兩個(gè)參數(shù)。
SELECT IFNULL(NULL, '默認(rèn)值');
等價(jià)于：
SELECT COALESCE(NULL, '默認(rèn)值');
COALESCE 可以接收多個(gè)參數(shù)：
SELECT COALESCE(nickname, real_name, username, '匿名用戶');
簡(jiǎn)單二選一時(shí)，IFNULL 和 COALESCE 都可以。多級(jí)兜底時(shí)，COALESCE 更合適。
另外，COALESCE 是標(biāo)準(zhǔn) SQL，跨數(shù)據(jù)庫(kù)兼容性更好。
COALESCE 和 NULLIF 經(jīng)常一起用
NULLIF(a, b) 的意思是：
如果 a = b，返回 NULL；否則返回 a。
例如：
SELECT NULLIF('', '');
結(jié)果：
NULL
所以它經(jīng)常和 COALESCE 組合，用來(lái)把空字符串、特殊值先轉(zhuǎn)成 NULL，再做兜底。
把空字符串當(dāng)成空值：
SELECT COALESCE(NULLIF(nickname, ''), '匿名用戶') AS display_name FROM users;
把 0 當(dāng)成無(wú)效金額：
SELECT COALESCE(NULLIF(amount, 0), 1) AS safe_amount FROM orders;
上面表示：如果 amount 是 0，先轉(zhuǎn)成 NULL，再兜底為 1。
COALESCE 和 CASE WHEN 的關(guān)系
下面兩段 SQL 邏輯接近。
SELECT COALESCE(nickname, real_name, username, '匿名用戶') AS display_name FROM users;
用 CASE WHEN 寫：
SELECT
CASE
WHEN nickname IS NOT NULL THEN nickname
WHEN real_name IS NOT NULL THEN real_name
WHEN username IS NOT NULL THEN username
ELSE '匿名用戶'
END AS display_name
FROM users;
只是判斷 NULL 并按順序兜底時(shí)，COALESCE 更短。
如果要寫復(fù)雜條件，比如狀態(tài)、金額區(qū)間、分?jǐn)?shù)等級(jí)，CASE WHEN 更適合。
常見坑一：空字符串不是 NULL
SELECT COALESCE('', '默認(rèn)值') AS result;
結(jié)果是空字符串，不是 默認(rèn)值。
因?yàn)椋?/p>
'' 是一個(gè)長(zhǎng)度為 0 的字符串，但它不是 NULL。
如果空字符串也要兜底：
SELECT COALESCE(NULLIF('', ''), '默認(rèn)值') AS result;
結(jié)果：
默認(rèn)值
常見坑二：參數(shù)類型盡量一致
不推薦：
SELECT COALESCE(NULL, 100, '未知');
參數(shù)里既有數(shù)字，又有字符串，后續(xù)參與計(jì)算或排序時(shí)容易引發(fā)隱式類型轉(zhuǎn)換。
推薦保持同一類結(jié)果：
SELECT COALESCE(CAST(score AS CHAR), '未知') AS score_text FROM users;
或者：
SELECT COALESCE(score, 0) AS score FROM users;
常見坑三：WHERE 里包字段可能影響索引
不推薦：
SELECT * FROM users WHERE COALESCE(mobile, backup_mobile) = '13800000001';
這種寫法把字段包進(jìn)函數(shù)表達(dá)式里，優(yōu)化器更難直接使用普通索引。
可以改成更明確的條件：
SELECT * FROM users WHERE mobile = '13800000001' OR (mobile IS NULL AND backup_mobile = '13800000001');
如果查詢特別高頻，可以考慮補(bǔ)充合適索引、生成列或在數(shù)據(jù)寫入時(shí)提前維護(hù)一個(gè)統(tǒng)一聯(lián)系方式字段。
常見坑四：不要把缺失數(shù)據(jù)直接偽裝成真實(shí)數(shù)據(jù)
COALESCE 很適合展示兜底，但不一定適合所有統(tǒng)計(jì)場(chǎng)景。
例如平均訂單金額：
SELECT AVG(COALESCE(amount, 0)) AS avg_amount FROM orders;
這表示：amount 為 NULL 的訂單也按 0 參與平均值計(jì)算。
如果 NULL 的真實(shí)含義是“未知金額”，這樣會(huì)拉低平均值。
另一種寫法：
SELECT AVG(amount) AS avg_amount FROM orders;
AVG 會(huì)忽略 NULL。
所以統(tǒng)計(jì)時(shí)要先確認(rèn) NULL 的業(yè)務(wù)含義：
NULL 是應(yīng)該當(dāng)作 0，還是應(yīng)該當(dāng)作未知并排除？
這個(gè)區(qū)別會(huì)直接影響報(bào)表結(jié)果。
實(shí)戰(zhàn)建議
- 展示字段缺省值，用 COALESCE(field, '默認(rèn)值')
- 多字段優(yōu)先級(jí)取值，用 COALESCE(a, b, c, '兜底值')
- 金額計(jì)算前，把可為空金額轉(zhuǎn)成 0，例如 COALESCE(discount, 0)
- SUM 沒有結(jié)果時(shí)返回 0，用 COALESCE(SUM(amount), 0)
- COUNT(*) 本身會(huì)返回 0，通常不需要再包 COALESCE
- 空字符串要當(dāng)成空值時(shí)，配合 NULLIF(field, '')
- 只是簡(jiǎn)單二選一，IFNULL 也能用；多級(jí)兜底優(yōu)先用 COALESCE
- WHERE 中不要隨手把索引字段包進(jìn) COALESCE
- 參數(shù)返回類型盡量一致，減少隱式轉(zhuǎn)換
- 統(tǒng)計(jì)場(chǎng)景里先判斷 NULL 的業(yè)務(wù)含義，再?zèng)Q定是否兜底為 0
總結(jié)
COALESCE 的核心就是“按順序找第一個(gè)非 NULL 的值”。
它看起來(lái)只是一個(gè)小函數(shù)，但在列表展示、報(bào)表統(tǒng)計(jì)、字符串拼接、多字段兼容、LEFT JOIN 補(bǔ)默認(rèn)值中都非常實(shí)用。
寫 SQL 時(shí)，只要遇到“字段可能為空，但結(jié)果不能空”的場(chǎng)景，就可以考慮 COALESCE。同時(shí)要記住兩個(gè)邊界：空字符串不是 NULL，統(tǒng)計(jì)里的 NULL 不一定等于 0。處理好這兩點(diǎn)，空值兜底就不容易出錯(cuò)。
到此這篇關(guān)于MySQL COALESCE 空值實(shí)戰(zhàn)詳解的文章就介紹到這了,更多相關(guān)MySQL COALESCE 空值內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL中的DATETIME 和 TIMESTAMP典型用法及關(guān)鍵區(qū)別
在MySQL里,DATETIME和TIMESTAMP雖都用于存儲(chǔ)日期時(shí)間數(shù)據(jù),但在存儲(chǔ)范圍、時(shí)區(qū)處理、存儲(chǔ)空間、默認(rèn)行為等方面差異顯著,下面通過(guò)本文給大家介紹MySQL中的DATETIME 和 TIMESTAMP典型用法及關(guān)鍵區(qū)別,感興趣的朋友一起看看吧2025-08-08
詳細(xì)分析mysql MDL元數(shù)據(jù)鎖
這篇文章主要介紹了mysql MDL元數(shù)據(jù)鎖的相關(guān)資料，文中講解非常細(xì)致，代碼幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-08-08
這篇文章主要介紹了簡(jiǎn)單介紹MySQL中索引的使用方法,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-05-05
Docker Dockerfile構(gòu)建MySQL并初始化數(shù)據(jù)方式
這篇文章主要介紹了Docker Dockerfile構(gòu)建MySQL并初始化數(shù)據(jù)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-04-04
這篇文章主要介紹了MySql與Navicat安裝教程,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-07-07
MySQL實(shí)現(xiàn)向表中添加多個(gè)字段 類型 注釋
這篇文章主要介紹了MySQL實(shí)現(xiàn)向表中添加多個(gè)字段 類型 注釋方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-04-04
Windows?11?和?Rocky?9?Linux?平臺(tái)?MySQL?8.0.33?簡(jiǎn)易安裝詳細(xì)教程
這篇文章主要介紹了Windows?11和Rocky9?Linux平臺(tái)MySQL8.0.33簡(jiǎn)易安裝教程,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-05-05
MySQL 的啟動(dòng)選項(xiàng)和系統(tǒng)變量實(shí)例詳解
這篇文章主要介紹了MySQL 的啟動(dòng)選項(xiàng)和系統(tǒng)變量,結(jié)合實(shí)例形式詳細(xì)分析了MySQL 啟動(dòng)選項(xiàng)和系統(tǒng)變量具體原理、功能、用法及操作注意事項(xiàng),需要的朋友可以參考下2020-05-05
Mysql存儲(chǔ)二進(jìn)制對(duì)象數(shù)據(jù)問題
這篇文章主要介紹了Mysql存儲(chǔ)二進(jìn)制對(duì)象數(shù)據(jù)問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-03-03
CentOS7環(huán)境下安裝MySQL5.5數(shù)據(jù)庫(kù)
大家好，本篇文章主要講的是CentOS7環(huán)境下安裝MySQL5.5數(shù)據(jù)庫(kù)，感興趣的同學(xué)趕快來(lái)看一看吧，對(duì)你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12