← 返回首页

SQL NULL 三值逻辑:7 个不报错却算错的查询

SQLNULL数据库

上周帮一位读者看他那张「上月消费超过 500 的用户」报表,SQL 只有四行,跑完没报错,导出的行数比后端接口少了一截。问题不在 join,也不在索引,而是那四行里有一个断言遇到的是 NULL:WHERE total != 0 这种写法在遇到 NULL 时,结果既不是真也不是假,而是第三种值,于是那一整行被无条件丢掉。SQL 的三值逻辑(three-valued logic)不是什么冷知识,它每天都在生产环境里静默改数据。

TL;DR:NULL 参与任何比较都不会得到 TRUE 或 FALSE,只会得到 UNKNOWN,而 WHERE 只放行 TRUE。这一条派生出七个坑:!= 吞行、NOT IN 恒空、聚合的分母变了、LEFT JOIN 退化成 INNER JOIN、ORDER BY 两库默认相反、GROUP BY 裸列一库静默一库报错、类型亲和让同一字面量出现两种答案。我用一张五行表把七个坑全部量了一遍,文末有一张可直接抄的自查清单。

实测环境与那张五行表

我实际在这台机器上跑完了全部对拍:Windows 11,Python 3.13.14 自带的 sqlite3(链接的 SQLite 库版本 3.53.1),以及本地起的一个 PostgreSQL 18.6 实例(x86_64-windows, compiled by msvc-19.44.35228, 64-bit,服务端编码 UTF8)。两个引擎喂同一份数据、跑同一批 SQL,下面的差异全部是现场打印出来的,不是从别人的博客转述的。

先确认版本,再建表——这两步能省掉后面所有「为什么你那跑得出来我这跑不出来」的争论:

python -c "import sqlite3; print(sqlite3.sqlite_version)"
# 3.53.1

psql -h 127.0.0.1 -p 5433 -U postgres -c "select version();"
# PostgreSQL 18.6 on x86_64-windows, compiled by msvc-19.44.35228, 64-bit

五行数据,故意让 customer 有一行是 NULL、amount 有两行是 NULL:

CREATE TABLE orders(id INTEGER PRIMARY KEY, customer TEXT, amount INTEGER);
INSERT INTO orders VALUES
 (1,'alice',100),
 (2,'bob',  NULL),
 (3,NULL,   50),
 (4,'alice',200),
 (5,'carol', NULL);
idcustomeramount备注
1alice100
2bobNULL金额缺失
3NULL50客户缺失
4alice200
5carolNULL金额缺失

一共 5 行。记住这个 5,后面每一次「少一行」都从这里扣。

坑一:不等于号会把 NULL 行整行吞掉

最省事的一句查询,想数「不是 alice 的订单有几单」:

SELECT count(*) FROM orders WHERE customer != 'alice';
-- SQLite: 2    PostgreSQL: 2

答案是 2。但表里有 5 行,alice 占 2 行,剩下 3 行本该都算进去——那行 customer IS NULL 被吃掉了。换成 <> 一样是 2:

SELECT count(*) FROM orders WHERE customer <> 'alice';
-- 2

SQLite 官方文档在运算符一节写得很直白:「All operators generally evaluate to NULL when any operand is NULL, with specific exceptions as stated below.」PostgreSQL 官方文档在比较函数一节用的是同一套语义:「Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null.」两个引擎在这里完全一致,所以这不是引擎 bug,是 SQL 标准本身的规则。

关键在于 WHERE 只接受 TRUE。UNKNOWN 和 FALSE 一样被过滤掉,但两者含义完全不同:FALSE 是「我确定它不满足」,UNKNOWN 是「我不知道」。把「不知道」当「不满足」处理,就是把缺失当成否。

实测代码量一下这层的厚度:

SELECT NULL AND 0, NULL AND 1, NULL OR 0, NULL OR 1, NOT NULL;
-- SQLite:     0 | NULL | NULL | 1 | NULL
-- PostgreSQL: f | NULL | NULL | t | NULL

NULL AND 0 在两个引擎上都是假(因为 0 已经是假,与运算是短路的),NULL OR 1 都是真。除此之外全是 NULL。SQLite 文档把这条例外写成了两句话:「AND evaluates to 0 (false) when the other operand is false; and OR evaluates to 1 (true) when the other operand is true.」

修法只有两条,选一条即可:

-- 写法 A:显式把 NULL 排掉,再比较
SELECT count(*) FROM orders
WHERE customer IS NOT NULL AND customer <> 'alice';   -- 2 行

-- 写法 B:把 NULL 单独算一类,跟业务确认它该算哪边
SELECT count(*) FROM orders WHERE customer IS NULL;      -- 1 行

如果业务语义是「所有不叫 alice 的」,正确写法是 WHERE customer IS NULL OR customer != 'alice',返回 3。差的那一行就是报表对不上的那一行。

坑二:NOT IN 的子查询里混进一个 NULL,结果恒为空

这个坑比上一个狠,因为它不是少一行,而是直接把整个结果集清零,而且不报错。

SELECT count(*) FROM orders WHERE id NOT IN (SELECT amount FROM orders);
-- SQLite: 0    PostgreSQL: 0

amount 列里有两个 NULL,于是这句子查询展开后等价于 id NOT IN (100, NULL, 50, 200, NULL)。任何 id 与 NULL 比 != 都得到 UNKNOWN,整串 OR 里只要有一个 UNKNOWN,这一行就出局。5 行全部出局,返回 0。

换成 NOT EXISTS 就正常:

SELECT count(*) FROM orders
WHERE NOT EXISTS (SELECT 1 FROM orders x WHERE x.amount = orders.id);
-- SQLite: 5    PostgreSQL: 5

同一份数据、同一个意图,0 和 5 的差别。注意 NOT EXISTS 走的是「逐行判断存在性」,NULL 在 x.amount = orders.id 里同样产出 UNKNOWN,但 UNKNOWN 在 EXISTS 里被当作「不存在」,于是这一行被保留——这正是我们要的语义。SQL 标准规定 EXISTS 只返回真或假,永远不返回 UNKNOWN,这是它比 NOT IN 可靠的根本原因。

SQLite 文档还点出了一个更容易被忽略的细节:「The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...". In other words, the values to the right of the IN operator ... are considered to have no affinity」。右边的列表元素被剥掉了亲和性,所以 IN 的比较规则和普通 = 不完全一样——这一点在第七个坑里还会咬人。

列表形态的 NOT IN 同样中招,只是范围小:

SELECT count(*) FROM orders WHERE amount NOT IN (100,200);                    -- 1
SELECT count(*) FROM orders WHERE amount NOT IN (100,200) OR amount IS NULL;  -- 3

1 和 3,差的是两行金额缺失的订单。

坑三:聚合跳过 NULL,平均值的分母不是行数

count(*) 和 count(列) 是两个不同的函数,虽然名字只差一对括号:

SELECT count(*), count(amount) FROM orders;
-- SQLite: 5, 3    PostgreSQL: 5, 3

SQLite 官方文档的原话是:「The count(X) function returns a count of the number of times that X is not NULL in a group. The count(*) function (with no arguments) returns the total number of rows in the group.」所以「有 5 单」和「有 5 个金额」不是一回事。

更贵的是平均值。同一张表:

SELECT sum(amount), avg(amount), round(avg(amount),2) FROM orders;
-- SQLite: 350 | 116.66666666666667 | 116.67

SELECT round(350.0/5, 2);
-- 70.0

avg 给的是 116.67,因为它算的是 350 ÷ 3(只数非 NULL 的行)。用「总额 ÷ 订单数」算出来是 70。同一句「平均客单价」有两个答案,相差 66.7%。这两个数字都「正确」,取决于你要回答的是「下单的人平均花多少」还是「所有订单平均多少」——但代码里通常只写了一个 avg(),没人检查分母。

count(distinct) 会在同一个问题上再制造一次分裂:

SELECT count(DISTINCT customer) FROM orders;                            -- 3
SELECT count(*) FROM (SELECT customer FROM orders GROUP BY customer);   -- 4

「有几个不同的客户」——3 还是 4?count(distinct) 按定义忽略 NULL,所以是 3;GROUP BY 把 NULL 当成一个独立的组(SQLite 文档:「For the purposes of grouping rows, NULL values are considered equal.」),所以是 4。表里确实有一个「客户未知」的订单,它是第四类,但不是第四个客户。

还有一条只在 SQLite 上出现的分歧——空集上的 sum():

SELECT sum(amount), total(amount) FROM orders WHERE id > 999;
-- SQLite: NULL | 0.0

官方文档解释得很坦率:「If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it」。PostgreSQL 同样返回 NULL,但没有 total() 这个非标准函数,只能用 coalesce(sum(x),0)。

坑四:LEFT JOIN 的条件写进 WHERE,静默退化成 INNER JOIN

这条被讲过很多次,但很少有人量出具体少了几行。加一张客户表,其中 dave 没有任何订单:

CREATE TABLE customers(name TEXT PRIMARY KEY, tier TEXT);
INSERT INTO customers VALUES ('alice','gold'),('bob','silver'),('dave','bronze');
SELECT count(*) FROM orders o LEFT JOIN customers c ON o.customer = c.name;
-- 5 行:LEFT JOIN 保住了全部订单

SELECT count(*) FROM orders o LEFT JOIN customers c ON o.customer = c.name
WHERE c.tier <> 'bronze';
-- 3 行(两个引擎都是 3)

SELECT count(*) FROM orders o LEFT JOIN customers c ON o.customer = c.name
  AND c.tier <> 'bronze';
-- 5 行(条件留在 ON 里)

5 变 3,少了 2 行——正是那些在 customers 里匹配不到、右表全为 NULL 的订单(id 3 与 id 5)。WHERE c.tier <> 'bronze' 对右表为 NULL 的行同样产出 UNKNOWN,于是 LEFT JOIN 的「保留左表全部行」被 WHERE 抹掉了,语义上等价于 INNER JOIN。

要找「哪些订单没有匹配到客户」,不能写 WHERE c.name != 'x',要用 IS NULL:

SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON o.customer = c.name
WHERE c.name IS NULL;
-- (3, NULL) 与 (5, NULL)

一个可以直接执行的判据:如果 LEFT JOIN 后面跟着的 WHERE 里出现了右表的列,先问一句「右表匹配不上时这条件成立吗」;答案是不成立,那这个 LEFT JOIN 就是白写的。

坑五:ORDER BY 的 NULL 位置,两个引擎默认相反

这是本篇唯一一处两个引擎给出相反结果的地方,而且它直接影响分页。

SELECT customer FROM orders ORDER BY customer;
-- SQLite:     NULL, alice, alice, bob, carol
-- PostgreSQL: alice, alice, bob, carol, NULL

SQLite 把 NULL 排在最小:文档原话「A value with storage class NULL is considered less than any other value (including another value with storage class NULL).」PostgreSQL 把 NULL 当作最大:文档原话「By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.」

分页时这个差别会直接把页首内容换掉。造一张 10 行的表,其中 2 行是 NULL:

CREATE TABLE p(id INTEGER PRIMARY KEY, v INTEGER);
INSERT INTO p VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,NULL),(10,NULL);

SELECT id, v FROM p ORDER BY v LIMIT 4;
-- SQLite:     (9,NULL) (10,NULL) (1,1) (2,2)
-- PostgreSQL: (1,1) (2,2) (3,3) (4,4)

同一句 ORDER BY v LIMIT 4,首页 4 行只有 2 行重合,重合率 50%。如果这套代码从 SQLite 迁到 PostgreSQL(或者反过来),首页内容会整体错位,而 SQL 一行没改、接口一个字段没动。

修法是把顺序写死,别依赖默认值。SQLite 从 3.30 起就支持 NULLS FIRST / NULLS LAST:

SELECT id, v FROM p ORDER BY v NULLS LAST LIMIT 4;
-- (1,1) (2,2) (3,3) (4,4)  —— 与 PostgreSQL 默认结果完全一致

ORDER BY v NULLS LAST, id 这样写还有第二个好处:并列值有稳定的第二排序键,翻页时不会出现同一行既在第一页又在第二页。只写 ORDER BY v 而 v 有重复值时,页间重复和漏行是必然的,跟 NULL 无关。

坑六:GROUP BY 的裸列,一个引擎静默、一个引擎报错

同一句 SQL,在 SQLite 上能跑,在 PostgreSQL 上直接拒绝执行:

SELECT id, customer FROM orders GROUP BY customer;
-- SQLite:     4 行 (3,NULL) (1,alice) (2,bob) (5,carol)
-- PostgreSQL: ERROR:  column "orders.id" must appear in the GROUP BY clause
--                     or be used in an aggregate function

SQLite 返回了 4 行,id 分别是 3、1、2、5,看起来毫无规律。原因是它替你做了一次任意选择。官方文档写得毫不含糊:「Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset.」以及「in many cases the value for "b" is undefined」。文档还明说这是 SQLite 的扩展:「Most other SQL database engines disallow bare columns... This is considered a feature, not a bug.」

所以你在 SQLite 上跑通的报表 SQL,换到 PostgreSQL 会因为这条裸列直接报错;反过来,从 PostgreSQL 迁到 SQLite 更危险——原来报错的 SQL 现在能跑,但 id 是随手挑的,值可能每次都不一样(文档:「The particular row chosen might change from one invocation to the the next.」)。

唯一的例外是 min() / max():只有一个 min/max 聚合时,裸列会取到那一行。但并列值出现时又变成任意选:

SELECT id, customer, amount FROM orders GROUP BY customer HAVING max(amount);
-- SQLite: (3,NULL,50) (4,alice,200)

alice 那条给出 200 是对的(她有两单,最大 200),但这是 SQLite 的特例行为,不能当通用写法搬到别的库。

坑七:类型亲和让同一个字面量有两种答案

这条最隐蔽,因为它在 SQLite 上是「有时候对有时候错」,取决于比较的一侧有没有列。

先看没有列的时候:

SELECT 1 = '1';
-- SQLite: 0     PostgreSQL: t

SQLite 说 0,PostgreSQL 说真。SQLite 的理由是类型序:整数永远小于文本,所以整数 1 与文本 '1' 不相等(文档:「An INTEGER or REAL value is less than any other TEXT or BLOB value.」)。PostgreSQL 的理由是未知类型字面量会被折成整数,所以等于真。

一旦比较的一侧是列,SQLite 的答案就翻过来了:

CREATE TABLE t(id INTEGER PRIMARY KEY, n INTEGER, s TEXT);
INSERT INTO t(n,s) VALUES ('123','123'), ('123abc','123'), (45.9,'x');

SELECT count(*) FROM t WHERE n = '123';   -- 1  (整数列 vs 文本字面量,亲和把 '123' 转成 123)
SELECT count(*) FROM t WHERE s = 123;     -- 2  (文本列 vs 整数字面量,亲和把 123 转成 '123')
SELECT 1 = '1';                            -- 0  (两侧都没有列,不施加亲和)

这就是 SQLite 类型亲和(type affinity)的真实手感:官方文档的规则是「If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to the other operand.」,以及「Otherwise, no affinity is applied and both operands are compared as is.」有没有列,决定了走哪一条分支。

写入端同样静默。上面 INSERT 之后看一眼 typeof:

SELECT id, typeof(n), n FROM t;
-- (1,'integer',123)    '123' 被转成整数
-- (2,'text','123abc')  转不动,原样存成文本
-- (3,'real',45.9)      45.9 保留为 real

一个声明为 INTEGER 的列里,混着 integer、text、real 三种存储类,全程无警告。之后 ORDER BY n 会按类型序排,数字永远排在文本前面,而不是按数值大小。

Cast 也是「能转多少转多少」:

SELECT CAST('12abc' AS INTEGER), CAST('abc' AS INTEGER), '12abc'+0;
-- 12 | 0 | 12

'abc' 变 0,'12abc' 变 12,都不报错。PostgreSQL 在这里反而是救命的:

SELECT count(*) FROM orders WHERE note = 0;
-- ERROR:  operator does not exist: text = integer
-- HINT:  No operator matches the given name and argument types.
--        You might need to add explicit type casts.

SELECT 1 = '1a';
-- ERROR:  invalid input syntax for type integer: "1a"

同一句在 SQLite 上会安静地返回 0 行。

舍入也有两套并存的答案。SQLite 的 round() 一律远离零:

SELECT round(2.5), round(0.5), round(1.5), round(-0.5), round(2.675,2);
-- 3.0 | 1.0 | 2.0 | -1.0 | 2.67

PostgreSQL 同一个函数名,按参数类型走两套规则:

SELECT round(2.5::float8), round(0.5::float8), round(1.5::float8),
       round(-2.5::float8), round(2.5::numeric);
-- 2 | 0 | 2 | -2 | 3

float8 走银行家舍入(round half to even),numeric 走四舍五入。官方文档原话:「For numeric, ties are broken by rounding away from zero. For double precision, the tie-breaking behavior is platform dependent, but "round to nearest even" is the most common rule.」金额字段用 numeric 还是 float8,在这里会差出一分钱——对账时这一分钱要查半天。

两个附带的口径差异,一并记下:

SELECT 'ABC' LIKE 'a%';   -- SQLite: 1    PostgreSQL: f(要写 ILIKE)
SELECT 'ABC' GLOB 'a*';   -- SQLite: 0    (GLOB 大小写敏感)

以及唯一约束。两库默认都不把两个 NULL 视为相等,于是「唯一」列里可以塞进多个 NULL:

-- PostgreSQL 15 起才有这个显式开关
CREATE TABLE u2(x int, y text, UNIQUE NULLS NOT DISTINCT (x));
INSERT INTO u2 VALUES (NULL,'c'),(NULL,'d');
-- ERROR:  duplicate key value violates unique constraint "u2_x_key"
-- DETAIL:  Key (x)=(null) already exists.

官方文档对这一点的表述是:「By default, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns.」并且提醒「The default null treatment in unique constraints is implementation-defined according to the SQL standard」——这属于标准留白,不能假设别的库跟你一致。

Troubleshooting:六个真实报错与修法

下面六条全部是我在上面那两张表上现场跑出来的原文,不是从论坛抄的。有意思的是:SQLite 只在这组用例里报了两处,其余它选择沉默;PostgreSQL 六处全部报错。所以「换引擎之后突然一堆报错」往往不是新引擎更差,而是旧引擎一直在替你兜着。

报错一:select 里混了没分组的列

ERROR:  column "orders.id" must appear in the GROUP BY clause or be used in
        an aggregate function
LINE 1: SELECT id, customer FROM orders GROUP BY customer;
               ^

原因:SELECT 列表里出现了既不在 GROUP BY 里、也不在聚合函数里的列(坑六)。PostgreSQL 拒绝猜,SQLite 替你随便挑一行。

修法二选一——把列放进聚合,或者放进 GROUP BY:

SELECT min(id) AS any_id, customer FROM orders GROUP BY customer;
SELECT id, customer FROM orders ORDER BY customer;

如果确实想要「每组里金额最大的那条」,用窗口函数而不是裸列:

SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY customer ORDER BY amount DESC NULLS LAST) rn
  FROM orders
) x WHERE rn = 1;

报错二:在 WHERE 里用聚合函数

-- PostgreSQL
ERROR:  aggregate functions are not allowed in WHERE

-- SQLite(同一条 SQL)
sqlite3.OperationalError: misuse of aggregate function count()

原因:WHERE 在分组之前求值,聚合要等分组之后才有结果,两者顺序冲突。同一件事两个引擎给了两句不同的文案(aggregate functions are not allowed in WHERE 与 misuse of aggregate function count()),搜索时两句都要试。

修法:聚合条件写进 HAVING。

SELECT customer, count(*) FROM orders GROUP BY customer HAVING count(*) > 1;
-- alice | 2

报错三:WHERE 后面跟了一个非布尔列

ERROR:  argument of WHERE must be type boolean, not type integer
LINE 1: SELECT count(*) FROM orders WHERE amount;
                                          ^

原因:PostgreSQL 的 WHERE 严格要求布尔表达式。SQLite 把整数按「非零即真」处理,同一条 SQL 在它那儿返回 3 行(amount 为 100/50/200 的行,NULL 那两行被 UNKNOWN 过滤),一句提示都没有。这类「跨库可移植性陷阱」在 SQLite 上永远不会暴露。

修法:把意图写明。

SELECT count(*) FROM orders WHERE amount IS NOT NULL;   -- 3
SELECT count(*) FROM orders WHERE amount != 0;          -- 3(同样丢 NULL)

报错四:文本列和数字比较

ERROR:  operator does not exist: text = integer
LINE 1: SELECT count(*) FROM orders WHERE note = 0;
                                               ^
HINT:  No operator matches the given name and argument types. You might need
       to add explicit type casts.

原因:PostgreSQL 不做隐式跨类型比较(坑七)。SQLite 会按亲和规则悄悄转换,甚至把 'abc' 转成 0 之后参与比较。

修法:显式转型,或者用正确的字面量类型。

SELECT count(*) FROM orders WHERE note = '0';
SELECT count(*) FROM orders WHERE note::int = 0;   -- 有非数字内容时会抛错,这反而是好事

报错五:字面量根本转不成目标类型

ERROR:  invalid input syntax for type integer: "1a"
LINE 1: SELECT 1 = '1a';
                   ^

原因:PostgreSQL 在解析阶段就把未知类型字面量折成整数,'1a' 折不动,直接报错——注意它报的是「输入语法错误」而不是「比较失败」,因为错在解析,还没到比较。SQLite 对 1 = '1a' 返回 0,1 = '1' 也返回 0,两种错法看起来一样。

修法:参数化查询加应用层校验,别把用户输入直接拼进 SQL 的字面量位置。

-- 不要:f"SELECT * FROM orders WHERE id = {user_input}"
-- 要:  cur.execute("SELECT * FROM orders WHERE id = ?", (user_input,))

顺便提醒一个 Python 层的同类坑,我测试时也踩到了:

cur.execute("SELECT count(*) FROM orders WHERE amount = ?", (None,))   # 0 行
cur.execute("SELECT count(*) FROM orders WHERE amount IS ?", (None,))  # 2 行

绑定 None 就等价于 amount = NULL,恒不成立。想查空值必须把 IS 写进 SQL 文本里,不能指望把 = 参数化成万能比较。

报错六:唯一约束挡不住 NULL

ERROR:  duplicate key value violates unique constraint "u2_x_key"
DETAIL:  Key (x)=(null) already exists.

原因:UNIQUE 默认把两个 NULL 视为不相等(坑七末尾),所以插多少个 NULL 都不冲突。这条报错只在你显式写了 UNIQUE NULLS NOT DISTINCT(PostgreSQL 15 起)时才会出现——出现它说明约束按你的意图在干活,是好事。

修法:需要「最多一个空值」时用部分索引,比改约束更灵活。

CREATE UNIQUE INDEX u2_x_one_null ON u2(x) WHERE x IS NULL;

一份可以直接抄的自查清单

七个坑压缩成七行判据,贴进 code review 模板即可:

出现的写法先问一句改法
col != 'x' 或 col <> 'x'这一列会不会是 NULL加 col IS NOT NULL,或把 NULL 显式归到一侧
NOT IN (子查询)子查询那一列有 NULL 吗换 NOT EXISTS,或给子查询加 WHERE col IS NOT NULL
avg(col) / count(col)分母是行数还是非空数要行数用 count(),要总额除行数就写 sum(col)/count()
LEFT JOIN ... WHERE 右表列右表为空时这条件成立吗条件挪进 ON,或用 IS NULL 判孤儿
ORDER BY col有 NULL 吗、有并列值吗写 NULLS LAST(或 FIRST),再加唯一第二排序键
SELECT 非分组列 ... GROUP BY这列在别的库会报错吗包进聚合函数,或补进 GROUP BY,或上窗口函数
跨类型比较 / round() / LIKE目标跑在哪个引擎上显式 CAST,金额用 numeric,不敏感匹配用 ILIKE

还有两条结构性建议,比逐条修更省事:

常见问题

Q:既然 NULL 这么麻烦,能不能干脆给所有列加 NOT NULL 和默认值?

大部分业务表应该这么做,但「未知」和「零」是两种信息。金额缺失写成 0,会污染 avg()、sum() 和所有报表口径;客户未知写成空字符串,会让 count(distinct) 多出一类。真正该做的是区分「业务上不允许缺失」的列(加 NOT NULL)与「允许未知」的列(保留 NULL,并在查询里显式处理)。

Q:count(*) 和 count(1) 有区别吗?

没有。两个引擎都会把它当成「数行数」,与列内容无关。网上关于 count(1) 更快的说法在现代优化器上不成立。

Q:WHERE col != 'x' 少的那些行,怎么一次性找出来?

用 SELECT count(*) FROM t WHERE col IS NULL 单独数一次,和总数对一下,差额就是被三值逻辑吃掉的行数。

Q:为什么 PostgreSQL 报错而 SQLite 不报,是不是 SQLite 有问题?

不是缺陷,是取舍。SQLite 官方文档把裸列、动态类型这些行为明确定义为特性(「This is considered a feature, not a bug.」),换来的是极低的部署成本和极强的向后兼容。代价是有些错误要等你换引擎、或者等报表对不上时才发现。知道这个取舍,就知道该在哪一层加校验。

Q:迁移到 PostgreSQL 时,怎么快速找出所有会炸的 SQL?

先跑一遍完整测试集,把四条会报错的形态逐个 grep:裸列加 GROUP BY、WHERE 里的聚合、WHERE 后的非布尔列、跨类型比较。前三条通常只有几十处,第四条要靠类型标注——这也是 PostgreSQL 更适合长期维护的原因:它把问题提前到了执行期,而不是留到报表期。

结语

三值逻辑不是语法细节,它是 SQL 里唯一一处「不报错、不警告、结果就是不对」的大面积区域。我在同一台机器、同一份数据上量出的七个坑,都可以用一句话概括:NULL 不是值,它是「不知道」,而 WHERE 不接受「不知道」。

排查顺序建议从分母开始——先确认 count(*) 与 count(列) 的差额,再看 NOT IN 有没有混进 NULL,最后才去怀疑 join 和排序。这个顺序能覆盖大部分「报表少了几行」的事故。

如果你还想看同类的「默认值悄悄改变结果」的实测,可以接着读这几篇:

本文所有数字均在本机 Windows 11、SQLite 3.53.1、PostgreSQL 18.6 上跑出,命令可直接复制执行。文中不含任何联盟推广链接,也不含亚马逊推广参数。

👉 立即参与小米 MiMo 开放平台:国内领先的 AI 大模型开放平台,高性价比推理服务

👉 立即参与阿里云 AI:汇集爆款 AI 产品,热门模型专属权益优惠券助力企业创新加速

📌 本文由 AI 辅助生成并经人工审核发布 | TechPassive — AI 驱动的内容测试站点,专注于效率工具与 SaaS 真实评测

🔗 精选推荐工具

使用以下链接支持我们持续产出高质量内容(点击可直接前往购买):

作为亚马逊联盟会员,符合条件的购买会为我们带来佣金,不会增加你的购买成本。

☁️ DigitalOcean 云服务器 ⚡ Vultr 高性能 VPS 🤖 QoderWork 中国版(推荐有奖) ☁️ 阿里云爆款 AI 产品 📚 WordPress 实用书单 🧠 AI / 大模型书单 🏗️ 系统设计/云架构书单 🐳 Docker/K8s 书单 🐧 Linux 系统管理书单 🐍 Python 编程书单 🗄️ 数据库/PostgreSQL 书单 🚀 DevOps/SRE 书单 🤖 小米 MiMo 开放平台
← 返回首页