核心語法
-- 查詢字段為 NULL
SELECT * FROM tbl WHERE col IS NULL;
-- 查詢字段不為 NULL
SELECT * FROM tbl WHERE col IS NOT NULL;
實戰演示
準備數據
UPDATE test SET t_birth = NULL WHERE t_id = 1;
-- t_id=1 的 t_birth 置為 NULL
查詢驗證
mysql> SELECT * FROM test WHERE t_birth IS NULL;
+------+--------+-------+------------+
| t_id | t_name | t_password | t_birth |
+------+--------+-------+------------+
| 1 | name1 | ... | NULL |
+------+--------+-------+------------+
mysql> SELECT * FROM test WHERE t_birth IS NOT NULL;
+------+--------+-------+------------+
| t_id | t_name | t_password | t_birth |
+------+--------+-------+------------+
| 2 | name2 | ... | 2013-01-01 |
+------+--------+-------+------------+
常見誤區
| 錯誤寫法 | 原因 | 正確寫法 |
|---|
WHERE col = NULL | NULL 不參與任何比較運算，結果永遠為 UNKNOWN | WHERE col IS NULL |
WHERE col != NULL | 同理，永遠 UNKNOWN | WHERE col IS NOT NULL |
WHERE col = '' | 空字符串 ≠ NULL | 按業務意圖選擇 |
進階技巧
COALESCE(col, 'default')：將 NULL 替換為指定值參與運算/顯示IFNULL(col, 0)：MySQL 專用簡寫NULLIF(expr1, expr2)：兩值相等返回 NULL，否則返回 expr1
統計 NULL 個數
SELECT COUNT(*) - COUNT(col) AS null_count FROM tbl;
-- 或
SELECT SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) FROM tbl;
