🏠 总目录📚 本教程 附录A · 速查与三库差异 ←
📑 本页目录(点开跳转)

附录 A · 速查与三库差异对照

实验记录怎么读

本页原有耗时、内存、报错和环境值保留为原文记录;本轮内容审校未逐项复跑这些实验。版本、硬件、数据和测量方法都会影响结果,示例中的倍数也不是保证。需要复现时先核对依赖版本,再在同一输入下比较。

⏱ 54 分钟 | ⭐ 一页纸:语法速查 + SQLite / Postgres / MySQL 差在哪 + 哪个特性要哪个版本

📌 Ctrl+F 搜。不要通读。

🎯 一句话

三个库的 SQL 语义高度一致,分歧集中在两个地方:函数名(换个写法就行)和几条会让你【算错】的默认值(不换写法,直接错)。 ⭐ 第三节那张「会让你算错的八条」是这一页里唯一值得读一遍的部分,其余按需搜。

⚠️ 本页的标记约定(和 00 章一致):

标记 含义
实测 在 Windows 11 · Python 3.13.14 · SQLite 3.50.4 上跑过,输出抄回来的
🗓️ 未实跑 —— sqlite 无此语法 Postgres / MySQL 特有,本机跑不了,只给形状不给输出

⭐ 第五节把「本机到底支不支持」的逐条探测输出整个贴出来了 —— 那一节全部是实测。

📋 一、执行顺序(全教程最该背下来的一行)

FROM / JOIN  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  DISTINCT  →  ORDER BY  →  LIMIT
                                                     ↑
                                       窗口函数在这一步之后算

由此直接推出的四条规则(01 章展开讲):

规则 为什么
WHERE 里不能用 SELECT 起的别名 WHERE 比 SELECT 早跑,那时别名还不存在
ORDER BY 里可以用别名 它比 SELECT 晚跑
WHERE 里不能放聚合函数,要用 HAVING WHERE 在分组之前,那时还没有「组」这个东西
WHERE 里不能放窗口函数 窗口函数在 SELECT 之后才算,比 WHERE 晚太多

⭐ 过滤发生在三个不同的时刻,选错就是另一个意思:

写在哪 什么时候过滤 典型用途
ON 后面 ⭐ 连接的时候(外连接里:不匹配的行仍然保留,只是右侧全 NULL) 限制怎么连
WHERE 连接之后、分组之前 挑哪些行
HAVING 分组之后 挑哪些组

⚠️ 左连接里把条件从 ON 挪到 WHERE,会把左连接悄悄变回内连接 —— 02 章的题目。

🧰 二、按「你要干什么」查写法

你要干的 写法 ⚠️ 坑 / 章
数行 vs 数非空值 COUNT(*) vs COUNT(col) 后者跳过 NULL;03
条件计数 SUM(CASE WHEN 条件 THEN 1 ELSE 0 END) ⭐ 不动 WHERE,所以能和别的指标并排;10
条件计数(更短) COUNT(*) FILTER (WHERE 条件) SQLite / PG 有,⚠️ MySQL 没有
去重计数 COUNT(DISTINCT col) ⚠️ 不数 NULL;SQLite 不支持 COUNT(DISTINCT a, b)
排除某几个值 col NOT IN ('a','b') 💀 列表里一旦有 NULL,整个结果是空集;04
空值兜底 COALESCE(col, 0) 三库通用,优先用它而不是 IFNULL
把某个值变成 NULL NULLIF(col, 0) 常用于防除零:a / NULLIF(b, 0)
判两值是否「真的不同」(含 NULL) a IS DISTINCT FROM b SQLite 3.39+ / PG;MySQL 写 NOT (a <=> b)
每组取 Top-N ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC) 外层筛 = 1 07;PG 另有 DISTINCT ON
排名(并列怎么处理) RANK() 跳号 / DENSE_RANK() 不跳号 / ROW_NUMBER() 强行分先后 07
累计求和 SUM(x) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ⭐ 不写帧的默认帧不是这个;08
最近 N 行 ROWS BETWEEN N PRECEDING AND CURRENT ROW 数行;08
最近 N 天 RANGE BETWEEN N PRECEDING AND CURRENT ROW(ORDER BY 要是数值) 数值,有缺口时和上一行不是一回事;08
和上一行比(环比) x - LAG(x) OVER (ORDER BY d) LAG / LEAD 第三个参数是缺省值
行转列 SUM(CASE WHEN k='a' THEN v END) AS a, SUM(CASE WHEN k='b' THEN v END) AS b ⭐ 三库通用;别找 PIVOT,那是 SQL Server 的
找重复 GROUP BY 键 HAVING COUNT(*) > 1 10
两表差集 SELECT ... EXCEPT SELECT ... 或 NOT EXISTS (...) ⚠️ 别用 NOT IN(见上面那条 💀)
递归展开层级 / 血缘 WITH RECURSIVE x AS (锚点 UNION ALL 递归步) SELECT ... ⭐ 有环必须带 depth 刹车;06
游标分页 WHERE (updated_at, id) < (?, ?) ORDER BY updated_at DESC, id DESC LIMIT 20 行值比较;05
看执行计划 EXPLAIN QUERY PLAN <查询> SQLite 专有拼法,三家不同(见第六节);09

🔀 三、⭐ 会让你算错的八条差异

⚠️ 这八条不是「换个函数名」,是同一条 SQL 在三个库里给出【不同的数】。 换库迁移时,这八条要逐条对一遍。

① NULL 默认排在哪一头

实测(SQLite):
  ORDER BY v        → [None, 10, 20, 30]     ← NULL 在最前
  ORDER BY v DESC   → [30, 20, 10, None]     ← NULL 在最后
库 ORDER BY x ASC 时 NULL 在
SQLite 最前(实测)
Postgres 🗓️ 最后(NULL 被当成比任何值都大)
MySQL 🗓️ 最前

⭐ 一律显式写 ORDER BY x ASC NULLS LAST(SQLite 3.30+ / PG 支持,实测 SQLite 可用)。 🗓️ MySQL 没有 NULLS LAST,要用 ORDER BY (x IS NULL), x。

② 整数除法

实测(SQLite):SELECT 5/2, 5.0/2  →  (2, 2.5)
库 5/2
SQLite 2(实测,整数除整数取整)
Postgres 🗓️ 2(同上)
MySQL 🗓️ 2.5(⚠️ / 永远返回小数;要取整用 DIV)

💀 这条最容易在迁移时静默改变结果:一条算比例的 SQL 从 MySQL 搬到 Postgres, 成功数/总数 会从 0.83 变成 0,而且不报错。⭐ 保险写法:分子乘 1.0, 100.0 * a / b。

③ 除以零

实测(SQLite):SELECT 1/0, 1.0/0  →  (None, None)
库 1/0
SQLite NULL(实测)
Postgres 🗓️ 报错:division by zero
MySQL 🗓️ NULL(带一条 warning)

⭐ 三家都能用的写法:a / NULLIF(b, 0) —— 分母是 0 就变 NULL,不报错也不算错。

④ 字符串拼接符

实测(SQLite):SELECT 'a' || 'b'       →  'ab'
实测(SQLite):SELECT CONCAT('a','b')  →  'ab'      (3.44+ 才有 CONCAT)

⚠️ 两个竖线在 MySQL 里默认是逻辑「或」,不是拼接(除非开了 PIPES_AS_CONCAT)。 🗓️ 未实跑。⭐ 要三家通吃就用 CONCAT()。

⚠️ 但 CONCAT() 和竖线拼接对 NULL 的处理不一样:

实测(SQLite):SELECT 'a' || NULL       →  None      ← 一个 NULL 毁掉整串
实测(SQLite):SELECT CONCAT('a', NULL)  →  'a'       ← NULL 被当空串跳过

💀 拼主键的时候这条会咬人:a || '|' || b 里 b 是 NULL,整个键变 NULL, 那一行在后续 GROUP BY 里独自成组且看不出原因。⭐ 拼键之前一律 COALESCE。

⑤ 双引号

实测(SQLite):SELECT COUNT(*) FROM t WHERE g = "a"   →  能跑,返回 2 行
库 "a" 是什么
SQLite 先当列名,找不到就退化成字符串(实测,所以上面那句能跑)
Postgres 🗓️ 永远是列名,找不到就报错
MySQL 🗓️ 默认是字符串(除非开 ANSI_QUOTES)

⭐ 规则很简单:字符串一律单引号,标识符一律不引(真要引,SQLite 三种引号都吃: "x"、`x`、[x] 全部实测能跑,但那只是兼容包袱,别用)。

⑥ LIKE 认不认大小写

实测(SQLite):SELECT COUNT(*) FROM t WHERE g LIKE 'A'   →  2   ← 匹配上了小写的 'a'
实测(SQLite):ILIKE  →  OperationalError: near "ILIKE": syntax error
库 LIKE 'A' 匹配 'a' 吗
SQLite ✅ 匹配(实测)。⚠️ 但只对 ASCII 生效,非 ASCII 字符照旧区分大小写
Postgres 🗓️ ❌ 不匹配,要用 ILIKE
MySQL 🗓️ 看列的排序规则(collation),默认那套 ..._ai_ci 是不区分的

💀 这条是「同一条 SQL 在两个库返回不同行数」的最常见来源,而且两边都不报错。 ⭐ 想要确定行为就自己动手:WHERE LOWER(g) = LOWER('A') (⚠️ 代价是这样写用不上 g 上的索引 —— 09 章的题目)。

⑦ GROUP BY 后面能不能跟没聚合的列

实测(SQLite):SELECT g, v, COUNT(*) FROM t GROUP BY g   →  [('a', 10, 2), ('b', 30, 2)]

⚠️ v 既没在 GROUP BY 里也没被聚合,SQLite 照跑不误,随便挑组里某一行的值给你。

库 行为
SQLite 允许,返回组内任意一行的值(实测)
Postgres 🗓️ 报错(除非那列函数依赖于分组键)
MySQL 🗓️ 5.7 起默认开 ONLY_FULL_GROUP_BY,报错;关掉就和 SQLite 一样

⭐ Postgres 的严格是对的:那个值没有意义。要「组内某一行的完整信息」, 正确写法是窗口函数 ROW_NUMBER() ... = 1,不是裸列。

⑧ 类型:声明了长度也不管用

实测(SQLite):CREATE TABLE small(x VARCHAR(3));
              INSERT INTO small VALUES('abcdefgh');   ← 不报错
              SELECT x, LENGTH(x) FROM small  →  ('abcdefgh', 8)

⚠️ SQLite 是动态类型 + 类型亲和性:VARCHAR(3) 里的 3 完全不生效。 🗓️ Postgres 会报 value too long;MySQL 严格模式报错、非严格模式截断加 warning。

⭐ SQLite 3.37+ 可以建 STRICT 表(实测可用)把类型管起来:

CREATE TABLE t(a INT, b TEXT) STRICT;

⚠️ 另一个相关的坑:

实测(SQLite):SELECT '1' = 1, '1' = '1', 1 = 1.0   →  (0, 1, 1)

'1' = 1 是假 —— 文本和数字属于不同的存储类,文本永远「大于」数字。 🗓️ Postgres 和 MySQL 在这里会把字面量转成数字,结果是真。 ⭐ 所以「把数字存成字符串」这件事,在 SQLite 上会以查不出行的形式发作,而不是报错。

🧭 换到 MySQL 8.4:类型上限、实际值与模式分开看

上面的 SQLite 长度实验不能预测 MySQL。以下按 MySQL 8.4 文档核对,字符例子明确使用 utf8mb4;是否拒绝越界还要同时看 sql_mode。本段未启动 MySQL 服务。

问题可带走的规则别据此推出
INT、显示宽度与 UNSIGNEDINT 占 4 字节;有符号范围 −231…231−1,无符号为 0…232−1。旧写法 INT(11) 的 11 是显示宽度,不改变字节数或范围;整数显示宽度与 ZEROFILL 在 8.4 已弃用不是“最多存 11 位”;UNSIGNED 运算也不等于 C/C++ 环绕。无符号减法可能报越界,NO_UNSIGNED_SUBTRACTION 等模式会影响结果类型
CHAR(M) / VARCHAR(M)M 是字符上限。CHAR 有定长与尾空格处理语义,VARCHAR 按实际内容长度存储并保留尾空格;比较时尾空格是否重要还取决于 collation。非空格超长输入在严格模式报错,非严格模式可截断并警告不能把字符数当字节数,也不能仅凭“定长”断言总比 VARCHAR 快;具体物理占用还受引擎行格式影响
VARCHAR(100) / VARCHAR(10)允许的最大字符数不同;相同短值不按 100 或 10 字节预留。声明上限、编码、长度元信息、整行限制与索引键限制都可能影响设计不是固定十倍空间或固定性能倍数;上限更大也不自动意味着每条记录更慢
BOOL / BOOLEAN是 TINYINT(1) 的别名;0 为假,非零为真,类型本身不把取值锁死在 0/1。需要二值约束可显式声明 flag BOOLEAN NOT NULL CHECK (flag IN (0,1)),使用 8.4 默认执行的 CHECKTINYINT(1) 仍可存 2;CHECK 允许 UNKNOWN,所以仅写 CHECK 不能代替 NOT NULL。不要外推到旧版忽略 CHECK 的行为
TEXT / BLOBTEXT 按字符集及 collation 解释、比较;BLOB 按二进制字节解释、比较。普通 TEXT/BLOB 的载荷上限都是 65,535 字节,系列还有 TINY/MEDIUM/LONGTEXT 不是任意字节的等价替身;大载荷是否参与查询、索引与备份,会改变 I/O、传输与备份量,不能只看类型名。应用存储取舍接 AI 全栈的数据分层

拿同一个值做长度手算:'北京' 是 2 个字符,在 utf8mb4 中这两个汉字的内容编码为 6 字节,均能放入 VARCHAR(10) 与 VARCHAR(100)。它不占满各自上限。MySQL 的类型存储表还列长度元信息;InnoDB COMPACT/DYNAMIC 的记录格式又有具体规则,因此这里的 6 只算内容,不是“整行磁盘实占 6 字节”。索引与效率要在同一数据、字符集、行格式和查询计划下比较。

把内容放回记录看:这里选 InnoDB COMPACT 聚簇记录作教学示意。组成包括变长列长度信息、NULL 位图、5 字节记录头、非 NULL 列内容,以及聚簇记录的事务 ID、回滚指针。NULL 位图按可空列数预留位,NULL 值不写列内容;非 NULL 变长列还需长度信息。只有没有主键、也没有合适的全列非空唯一键时,才生成隐藏行 ID。

MySQL 8.4 的 InnoDB 默认行格式为 DYNAMIC,实际以表定义与配置为准;它与 COMPACT 的长列页外存储规则不同。上面只示意记录组成,不给固定整行字节数,也未计入页头、页目录等页开销;其他引擎与行格式分别核对。依据:行格式、聚簇键选择。

类型依据:数值属性、布尔别名与整数类型、CHAR/VARCHAR、TEXT/BLOB、存储需求、InnoDB 行格式、CHECK。

IP 地址的表示选择(MySQL 8.4):文本便于读;只存 IPv4 数值时要用 INT UNSIGNED,例如 255.255.255.255 是 4294967295,超出有符号 INT。兼容 IPv4/IPv6 可用 VARBINARY(16) 配 INET6_ATON/INET6_NTOA 转换;前者分别产生 4/16 字节,无效地址或 NULL 得到 NULL,须按业务校验处理。2001:0db8:0:0:0:0:0:1 与 2001:db8::1 表示同一二进制地址。

本例约定:先识别 IPv4-mapped IPv6(如 ::ffff:192.0.2.1),规范成对应 IPv4 后再转换,其余 IPv6 保留 16 字节;函数不会自动替你合并两种地址族表示。不要把 IPv4 的 4 字节直接写入 BINARY(16),被右补零后仍当原值。供索引的规范值与原始审计字符串分开;若查询用函数包住存储列,索引效果仍看实际计划,不预报提速。依据:地址转换、BINARY 填充。以上为文档说明,未运行 MySQL;代理后的可信客户端来源可选读 原部署入口。

🕒 DATETIME / TIMESTAMP:先问“墙上时间”还是“同一个时刻”

MySQL 8.4,普通无偏移字面量DATETIMETIMESTAMP
连接 time_zone 改变存的日期时间字段不会随会话时区自动换算写入按会话时区转 UTC;读出转回当前会话时区。列本身不保存原时区名
有效范围(这里取 fsp=0)1000-01-01 00:00:00 … 9999-12-31 23:59:591970-01-01 00:00:01 … 2038-01-19 03:14:07 UTC
精度与类型存储量基础 5 字节基础 4 字节
两者共同的条件均支持 fsp 0…6,省略为 0;小数秒再加 0…3 字节,这不是整行占用。默认值与自动更新应显式写 DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP;不要把旧的首个 TIMESTAMP 隐式规则当成通用规则,检查 explicit_defaults_for_timestamp

时区手推:连接 +08:00 写普通字面量 '2026-10-22 10:00:00',再切到 +00:00 读:TIMESTAMP 应显示 02:00:00,DATETIME 仍显示 10:00:00。这是文档规则推导,未实跑;实际设计还要统一连接时区、输入约定及显示层转换。依据:时间类型与范围、类型存储量、自动初始化属性。

🧯 还有一条不算「算错」但值得知道:LENGTH 数的是什么

实测(SQLite):SELECT LENGTH('北京'), LENGTH(CAST('北京' AS BLOB))  →  (2, 6)

SQLite / Postgres 的 LENGTH 数字符;🗓️ MySQL 的 LENGTH() 数字节, 要数字符得用 CHAR_LENGTH()。⚠️ 一个存中文的字段,MySQL 上 LENGTH 会是三倍。

🔧 四、换个写法就行的差异(语法糖对照)

⚠️ 这一节的非 SQLite 列全部 🗓️ 未实跑 —— sqlite 无此语法,只给形状。

要干的事 SQLite(实测) Postgres 🗓️ MySQL 🗓️
分页 LIMIT 20 OFFSET 40(也吃 LIMIT 40, 20) LIMIT 20 OFFSET 40 LIMIT 40, 20 或 LIMIT 20 OFFSET 40
当前时间 CURRENT_TIMESTAMP(⚠️ 没有 NOW()) NOW() / CURRENT_TIMESTAMP NOW() / CURRENT_TIMESTAMP
日期减 7 天 date(d, '-7 days') d - INTERVAL '7 day' DATE_SUB(d, INTERVAL 7 DAY)
截到月 strftime('%Y-%m', d) date_trunc('month', d) DATE_FORMAT(d, '%Y-%m')
两日期相差几天 julianday(a) - julianday(b) a::date - b::date DATEDIFF(a, b)
类型转换 CAST(x AS INT) CAST(x AS INT) 或 x::int CAST(x AS SIGNED)
聚合成一串 GROUP_CONCAT(x, ',') 或 STRING_AGG(x, ',') STRING_AGG(x, ',') GROUP_CONCAT(x SEPARATOR ',')
中位数 ⚠️ 没有,用窗口函数 + LIMIT/OFFSET 绕 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) ⚠️ 也没有,同样要绕
正则匹配 ⚠️ 默认没有 REGEXP 函数(宿主程序可自己注册) x ~ '模式' / x ~* '模式'(后者忽略大小写) x REGEXP '模式'
插入或更新 INSERT ... ON CONFLICT(k) DO UPDATE SET ... 同左 INSERT ... ON DUPLICATE KEY UPDATE ...
写完把行返回 ... RETURNING id(3.35+,实测) ... RETURNING id ⚠️ 没有
每组取一行的捷径 ⚠️ 没有,用 ROW_NUMBER() = 1 DISTINCT ON (g) ... ⚠️ 没有,同样用 ROW_NUMBER()
看有哪些列 PRAGMA table_info(t) information_schema.columns information_schema.columns 或 DESCRIBE t
看有哪些表 SELECT name FROM sqlite_master WHERE type='table' information_schema.tables 或 \dt SHOW TABLES
自增主键 INTEGER PRIMARY KEY(可选 AUTOINCREMENT) GENERATED ALWAYS AS IDENTITY 或 SERIAL INT AUTO_INCREMENT

🧹 删行、清表、删对象,不是一种语法糖

下面先限定为 MySQL 8.4 / InnoDB 的普通表;权限、外键、触发器与事务条件都属于语义,不能只背速度排序。

操作留下什么 / WHERE事务与自增约束与权限
DELETE FROM t WHERE ...删匹配行,保留定义;省略 WHERE 则删全表行未提交的 InnoDB DML 可 ROLLBACK;普通删除不重置自增计数遵循外键动作,触发行删除触发器;需要 DELETE 权限,读取条件列还需 SELECT
TRUNCATE TABLE t清空整表,保留可用表定义;无 WHEREDDL 隐式提交,不能靠用户 ROLLBACK 撤回;自增重置到起始值不触发 ON DELETE;被别的表外键引用的 InnoDB 表会被拒绝(自引用例外);需要 DROP 权限
DROP TABLE t表数据与定义一并删除;无 WHERE普通表 DDL 隐式提交;用户 ROLLBACK 不能恢复这个表,自增对象也不再存在有外键依赖须先按约束关系处理;需要 DROP 权限。TEMPORARY 的隐式提交规则另有例外

别把崩溃原子性读成事务回滚:支持原子 DDL 的引擎能保证 TRUNCATE 在服务器中断时整体提交或撤销,不代表执行完成后用户能用 ROLLBACK 反悔。MySQL 的 DROP TEMPORARY TABLE 虽不隐式提交,其删除本身仍不可由 ROLLBACK 恢复。

换库就要重新判断:SQLite 没有 TRUNCATE TABLE 语法,DELETE FROM t 的“truncate 优化”仍是 DELETE,不等于 MySQL DDL;SQLite 显式事务中的 DELETE / DROP TABLE 可回滚,AUTOINCREMENT 表 DELETE 清空后不会因此重置历史序号。PostgreSQL 18 的 TRUNCATE 则可随事务回滚,默认 CONTINUE IDENTITY,只有指定 RESTART IDENTITY 才重启相应序列;这些条件不能套回 MySQL。

下面只在新建的 SQLite 内存连接演示删行、删表与回滚,不连接任何既有数据库。可逐句执行:

CREATE TABLE scratch(id INTEGER PRIMARY KEY AUTOINCREMENT, v TEXT);
INSERT INTO scratch(v) VALUES ('a'), ('b');
BEGIN;
DELETE FROM scratch WHERE id = 1;
SELECT COUNT(*) FROM scratch;  -- 1
ROLLBACK;
SELECT COUNT(*) FROM scratch;  -- 2
BEGIN;
DROP TABLE scratch;
ROLLBACK;
SELECT COUNT(*) FROM scratch;  -- 2,定义和行均恢复
DELETE FROM scratch;
INSERT INTO scratch(v) VALUES ('c');
SELECT id FROM scratch;        -- 3,未自动重置为 1

依据:MySQL 8.4 的 DELETE、TRUNCATE、DROP、隐式提交;SQLite 的 DELETE、事务、AUTOINCREMENT;PostgreSQL 18 TRUNCATE。MySQL / PostgreSQL 部分为文档核对,不能拿这段 SQLite 实跑代替。

⭐ 一条经验:迁移时先搜日期函数。它是三家分歧最密集的地方,而且几乎每个报表都用到。

🧪 五、本机到底支不支持:逐条探测(全部实测)

⭐ 这一节不是从文档抄的,是在 SQLite 3.50.4 上逐条跑出来的:能跑就打印结果, 不能跑就打印它抛的错。你换个版本重跑一遍,就知道自己那台机器的边界在哪。

sqlite: 3.50.4
  OK   窗口函数 ROW_NUMBER            -> [(1, 1), (2, 2), (3, 1)]
  FAIL 窗口 QUALIFY                 -> OperationalError: near "ROW_NUMBER": syntax error
  OK   FILTER (WHERE ...)         -> [(2,)]
  OK   NULLS LAST                 -> [(10,), (20,), (30,)]
  OK   IS DISTINCT FROM           -> [(3,)]
  OK   FULL OUTER JOIN            -> [(5,)]
  OK   RIGHT JOIN                 -> [(2,)]
  OK   行值比较 (a,b)<(?,?)           -> [(2,)]
  OK   WITH RECURSIVE             -> [(3,)]
  OK   CTE MATERIALIZED           -> [(1,)]
  OK   GROUP_CONCAT               -> [('a|a|b|b',)]
  OK   STRING_AGG                 -> [('a|a|b|b',)]
  FAIL DISTINCT ON                -> OperationalError: near "ON": syntax error
  FAIL ILIKE                      -> OperationalError: near "ILIKE": syntax error
  OK   LIKE 大小写(ASCII)            -> [(2,)]
  OK   MySQL 式 LIMIT 1,2          -> [(2,), (3,)]
  OK   整数除法 5/2                   -> [(2, 2.5)]
  OK   CONCAT()                   -> [('ab',)]
  OK   双引号当字符串                    -> [(2,)]
  OK   '1' = 1 ?                  -> [(0, 1, 1)]
  OK   布尔字面量 TRUE                 -> [(1, 0, 2)]
  OK   裸列跟着 GROUP BY              -> [('a', 10, 2), ('b', 30, 2)]
  OK   RETURNING                  -> [(9,)]
  OK   UPSERT ON CONFLICT         -> []
  OK   EXPLAIN QUERY PLAN         -> [(2, 0, 216, 'SCAN t')]
  FAIL EXPLAIN ANALYZE            -> OperationalError: near "SELECT": syntax error
  OK   date/strftime              -> [('2026-07-04', '2026-07')]
  OK   日期加减 modifier              -> [('2026-06-27',)]
  FAIL INTERVAL '7 day'           -> OperationalError: no such column: INTERVAL
  FAIL NOW()                      -> OperationalError: no such function: NOW
  OK   CURRENT_TIMESTAMP          -> [('2026-08-11 12:09:49',)]
  OK   RANGE 帧 + 数值偏移             -> [(10,), (30,), (60,)]
  FAIL RANGE INTERVAL 帧           -> OperationalError: near "'2 day'": syntax error
  OK   STRICT 表                   -> []
  FAIL COUNT(DISTINCT a,b)        -> OperationalError: wrong number of arguments to function COUNT()
  FAIL pg 风格 ::int 转换             -> OperationalError: unrecognized token: ":"
  FAIL LATERAL                    -> OperationalError: near "SELECT": syntax error
  FAIL generate_series            -> OperationalError: no such table: generate_series
  FAIL array_agg                  -> OperationalError: no such function: array_agg
  FAIL information_schema         -> OperationalError: no such table: information_schema.columns
  FAIL PERCENTILE_CONT            -> OperationalError: near "(": syntax error
  FAIL REGEXP                     -> OperationalError: no such function: REGEXP

⭐ 值得单独拎出来的三条:

  1. 布尔字面量 TRUE -> (1, 0, 2) —— TRUE + TRUE = 2。SQLite 里布尔就是整数 1 / 0,没有独立的布尔类型。所以 SUM(条件) 直接就能当条件计数用。
  2. RANGE 帧 + 数值偏移 能跑,RANGE INTERVAL 帧 不能 —— SQLite 的 RANGE 只吃数值偏移。想按「最近 7 天」开窗,得先把日期转成数字(julianday(d))再 ORDER BY 它。 🗓️ Postgres 才支持 RANGE BETWEEN INTERVAL '7 day' PRECEDING AND CURRENT ROW。08 章有完整例子。
  3. QUALIFY 三家都没有 —— 它是 Snowflake / BigQuery / DuckDB 的方言。 在这三个库里筛窗口函数结果,只能外面套一层子查询。

🗓️ 六、哪个特性要哪个版本

⚠️ 「起始版本」一列取自各库的发行说明,不是实测;本机 SQLite 3.50.4 上, 表里标 ✅ 的 SQLite 项全部实测能跑(输出见第五节)。

特性 SQLite Postgres MySQL
WITH / CTE ✅ 3.8.3 ✅ 8.4 ⚠️ 8.0 起,5.7 没有
WITH RECURSIVE ✅ 3.8.3 ✅ 8.4 ⚠️ 8.0 起
窗口函数 ✅ 3.25 ✅ 8.4 ⚠️⚠️ 8.0 起
行值比较 (a,b) < (?,?) ✅ 3.15 ✅ ✅
FILTER (WHERE ...) ✅ 3.30 ✅ 9.4 ❌ 没有
NULLS FIRST / LAST ✅ 3.30 ✅ ❌ 用 ORDER BY (x IS NULL), x
UPSERT ✅ 3.24 ✅ 9.5 ✅(ON DUPLICATE KEY UPDATE)
RETURNING ✅ 3.35 ✅ ❌ 没有
CTE MATERIALIZED 提示 ✅ 3.35 ✅ 12 ❌ 没有
STRICT 表 ✅ 3.37 —(本来就强类型) —(本来就强类型)
FULL OUTER JOIN ✅ 3.39 ✅ ❌ 要用 UNION 拼
IS DISTINCT FROM ✅ 3.39 ✅ ❌ 用 NOT (a <=> b)
CONCAT() / STRING_AGG() ✅ 3.44 ✅ / ✅ ✅ / ❌

💀 最常撞的一条是窗口函数在 MySQL 5.7 上不存在。 一份在本地 SQLite 上写得好好的报表 SQL,扔到还没升级的 MySQL 5.7 上会直接语法错 —— 这是少数几个会当场报错的差异,反而是好事。

🚦 七、三家的执行计划怎么看

库 命令 特点
SQLite EXPLAIN QUERY PLAN <查询>(实测) ⚠️ 不真跑,只给形状;EXPLAIN ANALYZE 不存在(实测报错)
Postgres 🗓️ EXPLAIN (ANALYZE, BUFFERS) <查询> 加 ANALYZE 会真跑一遍,给真实耗时和真实行数
MySQL 🗓️ EXPLAIN <查询> / EXPLAIN ANALYZE <查询>(8.0.18+) FORMAT=JSON 给更细的成本估算

⚠️ 三家计划里最该盯的两处是一样的:扫的是索引还是全表、估算行数和真实行数差多少。 ⭐ 而「计划里写着索引名,它照样可能是全表扫」是 09 章整章的题目 —— 这里不重复。

🔍 MySQL 8.4 传统 EXPLAIN:五列先读对

显式用 EXPLAIN FORMAT=TRADITIONAL SELECT ... 看下面这组列;JSON / TREE 的组织不同,不要按列位置硬套。

列含义判断边界
type访问/连接类型,如 ALL、index、range、ref、eq_ref、constALL 是全表扫描;index 也可能是全索引扫描。不能单凭一个名字给整条查询定快慢
key实际选择的索引;possible_keys 只是候选有索引名不代表只读少量记录,也不代表覆盖查询
rows预计需检查的行数;InnoDB 中是估算不是已返回行数,更不是计时结果
filtered预计经表条件后留下的百分比100 表示没有进一步过滤;不是“过滤掉了 100%”
Extra补充操作,如 Using index、Using where、Using filesort、Using temporaryUsing index 指覆盖读取,Using filesort 表示额外排序,不保证一定写磁盘

只手算一行估计:若 rows = 1000、filtered = 20,预计交给下一连接步骤的行数约为 1000 × 20 / 100 = 200。这是示意数字,既不是实际返回 200 行,也不是在说整条查询仅检查 200 行。再与实际执行比较,才知道估算差在哪。

估算与实跑分开:MySQL 8.4 的 EXPLAIN ANALYZE 实际执行并给出 actual time、rows、loops,使用 TREE 格式;不能要求它同时输出传统五列。它支持 SELECT、TABLE 及多表 UPDATE / DELETE,不能泛化成所有 DML。PostgreSQL 18 的 ANALYZE 支持范围不同;执行写语句会发生副作用。练习应使用隔离测试数据,事务回滚也不保证撤销外部函数等所有副作用。

依据:MySQL 8.4 EXPLAIN 列与 Extra、MySQL 8.4 ANALYZE 支持范围、PostgreSQL 18 EXPLAIN。连接与索引选择的实验仍看 09 章,此处不重复展开。

🔒 八、这一页不讲的:SQL 注入

⭐ SQL 注入整块归 《AI 全栈》06b 列表接口与分页,本教程一个字不重复。

去那边的理由(那一章真的讲了这些): 排序字段被直接拼进 ORDER BY 是最常见的注入面(ORDER BY 后面能放子查询、能放 CASE), 而且 ⚠️ 参数化占位符 ? 在这里救不了你 —— ? 只能替换「值」,不能替换列名和关键字, ORDER BY ? 不报错但静默失效(参数被当常量,返回插入序 —— 比报语法错更糟)。没有参数化退路,只能靠白名单。

🔗 这一页连到哪里

相关的地方 为什么
00 怎么用这份教程 本页的两个标记(实测 / 🗓️)在那里定义,还有为什么整套教程跑在 SQLite 上
01 一条查询是怎么跑的 第一节那行执行顺序的完整展开,以及它怎么解释一半的报错
09 计划里的连接与索引失效 第七节只列了三家的 EXPLAIN 拼法,「计划该怎么读、写法怎么废掉索引」在那一章
AI 全栈 · 06 关系数据库 ⭐ 本页只讲查询语法,建表 / 建索引 / 迁移 / 连接池全在那边
AI 全栈 · 06b 列表接口与分页 ⭐ SQL 注入、COUNT(*) 的成本、游标分页在接口层怎么用
AI 全栈 · 附录B 技术栈对照 同样是「换一套技术栈要改什么」的对照表,那边是 Web 栈这边是数据库

🗓️ 这一页会过期的部分:第六节的版本号、第四节的语法糖对照。 ⭐ 不会过期的部分:第一节的执行顺序、第三节那八条语义差异 —— 它们是标准和实现取舍决定的,比任何一个版本号活得久。

下一节 👉 回到首页

打卡记录保存在你的浏览器里,首页能看到总进度