NULL不是空——数据库里最反直觉的设计，90%新人踩过的坑

关键词NULL、数据库空值、三值逻辑、NULL陷阱、SQL空值处理大家好我是小耶写功课只是为了我踩过的坑你们别再踩了数据库里有一个设计让无数新手怀疑人生——NULL。你以为NULL代表空、“什么都没有”、“等于空白”。但数据库告诉你WHERE column NULL查不出任何数据。你以为COUNT(*)能统计所有行但COUNT(column)却漏掉了一些。你写了一个if (value null)的判断结果数据死活对不上。这一切的根源在于——NULL在数据库里根本不是空值而是一个特殊标记表示未知或不适用。今天把NULL这件事讲清楚帮你避开那些反直觉的坑。几个先搞明白的概念NULL的本质NULL不是空字符串’不是0不是false。它是一个独立的特殊值表示这个字段当前没有确定的值。你可以把它理解成一张问卷上的未作答——它不是否也不是空白而是我不知道。三值逻辑大多数编程语言只有TRUE和FALSE两种结果。但数据库引入了NULL之后逻辑运算变成了三值TRUE、FALSE、UNKNOWN。当运算中涉及NULL时结果可能就是UNKNOWN——而WHERE条件只返回TRUE的行UNKNOWN被当作FALSE处理这就是为什么很多查询查不出数据的根本原因。空字符串 vs NULL空字符串’‘是一个确定的值——它就是一个长度为0的字符串。NULL表示没有值。’和NULL在数据库中是完全不同的两个概念。NULL的五大反直觉陷阱陷阱一WHERE column NULL 查不出任何数据这是新手必踩的坑。-- 你以为这样能查出所有phone为空的记录SELECT*FROMusersWHEREphoneNULL;-- 结果0行-- 正确写法SELECT*FROMusersWHEREphoneISNULL;为什么因为NULL不等于任何东西包括它自己。在数据库中NULLNULL→ UNKNOWN不是TRUENULL!NULL→ UNKNOWN不是TRUENULL NULL的结果是UNKNOWN而WHERE只返回TRUE的行所以查出来是0行。必须用IS NULL或IS NOT NULL来判断。陷阱二COUNT(column) 忽略NULL值-- 假设users表有100行其中10行phone为NULLSELECTCOUNT(*)FROMusers;-- 100统计所有行SELECTCOUNT(phone)FROMusers;-- 90忽略NULL值COUNT()统计行数不管字段是什么。COUNT(column)只统计该字段非NULL的行数。如果你想知道有多少人有手机号用COUNT(phone)如果你想知道总共有多少人用COUNT()。陷阱三NULL参与运算结果还是NULLSELECT1NULL;-- NULLSELECThello||NULL;-- NULLSELECTNULL0;-- UNKNOWN不是FALSESELECTNULL;-- UNKNOWN不是FALSESELECTNULLANDTRUE;-- UNKNOWN不是FALSE任何值和NULL运算结果都是NULL。这在实际业务中会造成很多bug-- 计算员工总薪资SELECTsalarybonusFROMemployees;-- 如果某个员工bonus是NULL整条记录的总薪资就是NULL正确做法是用COALESCE把NULL替换为默认值SELECTsalaryCOALESCE(bonus,0)FROMemployees;-- NULL变成0计算正常陷阱四NOT IN 遇到NULL整个查询结果为空这是最隐蔽的一个。-- 假设子查询返回了 (1, 2, NULL)SELECT*FROMusersWHEREidNOTIN(SELECTuser_idFROMorders);-- 如果orders表里有user_id为NULL的记录整个查询返回0行为什么因为NOT IN在底层展开成WHEREid!1ANDid!2ANDid!NULLid ! NULL的结果是UNKNOWN整个AND表达式变成UNKNOWNWHERE不返回任何行。解决办法用NOT EXISTS替代NOT IN或者在子查询中排除NULL-- 方案一NOT EXISTSSELECT*FROMusers uWHERENOTEXISTS(SELECT1FROMorders oWHEREo.user_idu.id);-- 方案二子查询排除NULLSELECT*FROMusersWHEREidNOTIN(SELECTuser_idFROMordersWHEREuser_idISNOTNULL);陷阱五排序时NULL的位置不同数据库对NULL的排序处理不同SELECT*FROMusersORDERBYphoneASC;-- MySQLNULL排在最前面-- Oracle/PostgreSQLNULL排在最后面如果你不确定NULL的排序行为最好显式指定-- PostgreSQLSELECT*FROMusersORDERBYphoneASCNULLSFIRST;SELECT*FROMusersORDERBYphoneDESCNULLSLAST;-- MySQL用IF/CASE处理SELECT*FROMusersORDERBYIF(phoneISNULL,1,0),phoneASC;NULL的正确打开方式判断NULL用IS NULL或IS NOT NULL别用或!。处理NULL参与计算用COALESCE提供默认值。SELECTCOALESCE(phone,未登记)FROMusers;SELECTsalaryCOALESCE(bonus,0)FROMemployees;聚合函数对NULL的态度函数对NULL的处理COUNT(*)统计所有行不忽略NULLCOUNT(column)忽略该列的NULL值SUM(column)忽略NULL值AVG(column)忽略NULL值且分母也不计NULL行MAX/MIN忽略NULL值GROUP BYNULL值被分到同一组避免NULL的设计思路如果业务上某个字段必须有值在建表时就加上NOT NULL约束并设置默认值CREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(50)NOTNULL,statusTINYINTNOTNULLDEFAULT0,phoneVARCHAR(20)-- 允许NULL因为确实可能没登记);能NOT NULL的字段就别留NULL。NULL存在的每一处都是未来查询时可能踩的坑。总结NULL不是空值是未知。记住三句话判断NULL用IS NULL别用NULL参与运算结果是NULL用COALESCE处理NOT IN遇到NULL会吞掉所有结果改用NOT EXISTS建表时能NOT NULL就别留NULL少一个NULL少十个bug。小耶在手SQL不愁。还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~参考文献SQL-92标准 - 三值逻辑规范《SQL反模式》第12章NULL的处理陷阱MySQL 8.0 Reference Manual - Working with NULLhttps://dev.mysql.com/doc/refman/8.0/en/working-with-null.htmlPostgreSQL Documentation - Null Valueshttps://www.postgresql.org/docs/current/functions-comparison.html