05.7.5 方言对照与踩坑
所属:全栈开发面试题库 / 05 数据库与存储 / 05.7 SQL 语言与查询技巧
覆盖范围
- MySQL / PostgreSQL / SQL Server 常见语法差异
- 分页、字符串拼接、类型转换、日期函数的方言
- 隐式转换与字符集排序规则陷阱
- 时区函数与时间类型差异
- NULL 排序默认行为差异
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.7.5-001 | 三库分页写法差异 | 基础 | ★★★★★ |
| 05.7.5-002 | 字符串拼接与类型转换差异 | 进阶 | ★★★★ |
| 05.7.5-003 | MySQL 隐式转换有什么坑 | 高级 | ★★★★★ |
| 05.7.5-004 | 时间与时区函数差异 | 进阶 | ★★★★ |
| 05.7.5-005 | NULL 排序默认行为差异 | 进阶 | ★★★ |
| 05.7.5-006 | 自增列与返回主键差异 | 基础 | ★★★★ |
| 05.7.5-007 | 排序规则(collation)不一致导致 JOIN 失败 | 高级 | ★★★ |
05.7.5-001 MySQL、PostgreSQL、SQL Server 的分页写法有什么差异?
频率 ★★★★★ | 难度 基础
一句话结论
MySQL/PostgreSQL 用 LIMIT n OFFSET m;SQL Server 用 OFFSET m ROWS FETCH NEXT n ROWS ONLY(SQL Server 2012+),旧版用 ROW_NUMBER 包装。
代码 / 示例
-- MySQL / PostgreSQL
SELECT * FROM t ORDER BY id LIMIT 20 OFFSET 100;
-- SQL Server 2012+
SELECT * FROM t ORDER BY id OFFSET 100 ROWS FETCH NEXT 20 ROWS ONLY;
-- SQL Server 旧版
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM t
) x WHERE rn BETWEEN 101 AND 120;
常见误区
- 误区:MySQL 的
LIMIT 100,20语义是 offset=20。正解:LIMIT offset,count第一个数是 offset,第二个是 count。
05.7.5-002 字符串拼接和类型转换有什么方言差异?
频率 ★★★★ | 难度 进阶
一句话结论
MySQL 用 CONCAT() 或 ||(需 sql_mode 开启),PostgreSQL 用 || 直接拼接,SQL Server 用 +;类型转换 MySQL 用 CAST(...)/CONVERT,PG 直接 ::,SQL Server 用 CAST/CONVERT。
代码 / 示例
-- MySQL:CONCAT
SELECT CONCAT(first_name, ' ', last_name) FROM users;
-- PostgreSQL:||
SELECT first_name || ' ' || last_name FROM users;
-- SQL Server:+
SELECT first_name + ' ' + last_name FROM users;
-- 类型转换
-- MySQL: CAST('1' AS UNSIGNED)
-- PG: '1'::int
-- SQL Server: CAST('1' AS INT) 或 CONVERT(INT, '1')
常见误区
- 误区:MySQL 的
||是逻辑或。正解:默认是逻辑或,开启PIPES_AS_CONCAT后才是拼接;跨库用 CONCAT 最稳。
05.7.5-003 MySQL 隐式转换有什么坑?
频率 ★★★★★ | 难度 高级
一句话结论
MySQL 在 WHERE 条件中类型不匹配时会做隐式转换,可能让索引失效、把字符串列转成数字,或导致非预期的比较结果;典型坑是字符串数字列与数字比较、字符集不同的列 JOIN。
概念解释
- 隐式转换规则:字符串与数字比较时,字符串被转成数字。
- 索引失效:对索引列施加函数/转换会导致索引失效。
- 字符集转换:JOIN 两列字符集不同时会临时转换,索引失效。
代码 / 示例
-- phone 是 varchar(20),但传数字比较
SELECT * FROM users WHERE phone = 13800000000;
-- MySQL 把 phone 每行转成数字再比较,索引失效,全表扫描
-- 正确:传字符串
WHERE phone = '13800000000'
-- 字符串数字前导零:
-- '123abc' = 123 为 TRUE(MySQL 把字符串转数字,取前导数字)
-- 这会导致 WHERE col = 0 命中所有非数字开头的字符串
常见误区
- 误区:
varchar列传数字没影响。正解:索引失效且可能误匹配。 - 误区:JOIN 两列字符集不同只是慢。正解:会报 "Illegal mix of collations" 错误或索引失效。
追问
- 问:怎么排查隐式转换?答:看 EXPLAIN 是否出现
Using where全表扫描,对比列与参数类型。
05.7.5-004 时间与时区函数有什么差异?
频率 ★★★★ | 难度 进阶
一句话结论
MySQL 用 NOW()/CURDATE()、DATE_FORMAT、INTERVAL;PG 用 NOW()/CURRENT_DATE、to_char、INTERVAL;SQL Server 用 GETDATE()/SYSDATETIME()、FORMAT/DATEADD;TIMESTAMP 与 DATETIME 在时区处理上行为不同。
代码 / 示例
-- MySQL
SELECT NOW(), DATE_FORMAT(NOW(), '%Y-%m-%d'), NOW() - INTERVAL 1 DAY;
-- PostgreSQL
SELECT NOW(), to_char(NOW(),'YYYY-MM-DD'), NOW() - INTERVAL '1 day';
-- SQL Server
SELECT GETDATE(), FORMAT(GETDATE(),'yyyy-MM-dd'), DATEADD(day,-1,GETDATE());
-- 时区:
-- MySQL TIMESTAMP 存 UTC 并按会话时区转换;DATETIME 不转换
-- PG timestamp 无时区,timestamptz 存 UTC
常见误区
- 误区:MySQL DATETIME 会自动转时区。正解:只有 TIMESTAMP 转换;DATETIME 字面存取。
- 误区:跨时区业务用本地时间。正解:统一存 UTC,展示层按用户时区格式化。
05.7.5-005 NULL 排序默认行为有什么差异?
频率 ★★★ | 难度 进阶
一句话结论
PostgreSQL 默认 NULL 最大(ASC 时 NULL 在最后);MySQL 默认 NULL 最小(ASC 时 NULL 在最前);SQL Server 默认 NULL 在最前;跨库排序要用 NULLS FIRST/LAST 显式指定。
代码 / 示例
-- PG 支持 NULLS FIRST/LAST
SELECT * FROM t ORDER BY create_time ASC NULLS LAST;
-- MySQL 8 也支持 NULLS FIRST/LAST
SELECT * FROM t ORDER BY create_time IS NULL, create_time;
-- MySQL 旧版用表达式模拟:
ORDER BY CASE WHEN create_time IS NULL THEN 1 ELSE 0 END, create_time
常见误区
- 误区:ASC 时 NULL 位置三库一致。正解:PG 与 MySQL 默认相反,迁移后排序结果可能变化。
05.7.5-006 插入后返回自增主键的写法有什么差异?
频率 ★★★★ | 难度 基础
一句话结论
MySQL 用 LAST_INSERT_ID() 或 RETURNING(8.0+ 部分版本);PostgreSQL 用 INSERT ... RETURNING id;SQL Server 用 SCOPE_IDENTITY() 或 OUTPUT INSERTED.id。
代码 / 示例
-- MySQL
INSERT INTO users(name) VALUES('a');
SELECT LAST_INSERT_ID();
-- PostgreSQL / SQL Server 2008+
INSERT INTO users(name) VALUES('a') RETURNING id;
-- SQL Server
INSERT INTO users(name) OUTPUT INSERTED.id VALUES('a');
常见误区
- 误区:SELECT @@IDENTITY 是安全的。正解:SQL Server 的 @@IDENTITY 会受触发器影响,应用 SCOPE_IDENTITY()。
05.7.5-007 JOIN 两列字符集(collation)不一致会怎样?
频率 ★★★ | 难度 高级
一句话结论
MySQL 中 JOIN 两列字符集或排序规则不同时,轻则索引失效、重则报 "Illegal mix of collations" 错误;需在建表时统一字符集与 collation,或用 CONVERT 显式转换。
代码 / 示例
-- 报错:
-- Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT)
SELECT * FROM a JOIN b ON a.name = b.name;
-- 临时解法:
JOIN b ON a.name = b.name COLLATE utf8mb4_general_ci
常见误区
- 误区:字符集相同 collation 就无所谓。正解:collation 决定比较规则,不同 collation 即使都是 utf8mb4 也会冲突。
追问
- 问:生产怎么统一?答:全库统一
utf8mb4 + utf8mb4_unicode_ci,建库时指定默认字符集。
05.7.5-008 三库自增主键差异?
频率 ★★ | 难度 基础
一句话结论
MySQL 用 AUTO_INCREMENT;PG 用 SERIAL/IDENTITY;SQL Server 用 IDENTITY(1,1);插入后返回主键方式不同(见 05.7.5-006)。
05.7.5-009 隔离级别默认值差异?
频率 ★★ | 难度 进阶
一句话结论
MySQL InnoDB 默认 REPEATABLE READ;PostgreSQL 默认 READ COMMITTED;SQL Server 默认 READ COMMITTED;迁移时注意并发行为差异。
05.7.5-010 MySQL sql_mode 有什么坑?
频率 ★★ | 难度 进阶
一句话结论
不同版本 sql_mode 不同,严格模式(STRICT_TRANS_TABLES)下插 NULL 或超长会报错;老版本宽松模式下数据被截断,迁移到新版本要检查。
面试高频追问
问:MySQL 隐式转换为什么会索引失效?
答:对索引列施加函数或转换会导致索引失效;WHERE phone = 13800000000(phone 是 varchar)会把每行转数字,全表扫描。
问:NULL 排序三库默认一致吗?
答:不一致。PG 默认 NULL 最大,MySQL 默认 NULL 最小;跨库用 NULLS FIRST/LAST 显式指定。
问:collation 不一致会怎样?
答:JOIN 两列字符集或排序规则不同时,轻则索引失效,重则报 Illegal mix of collations 错误。
问:时区函数三库有什么差异?
答:MySQL TIMESTAMP 存 UTC 按会话时区转换,DATETIME 不转换;PG timestamptz 存 UTC;SQL Server 用 GETDATE()/SYSDATETIME()。
核心要点速记
| 功能 | MySQL | PostgreSQL | SQL Server | ||
|---|---|---|---|---|---|
| 分页 | LIMIT n OFFSET m | LIMIT n OFFSET m | OFFSET..FETCH | ||
| 字符串拼接 | CONCAT() | + | |||
| 时间 | NOW() | NOW() | GETDATE() | ||
| 自增 | AUTO_INCREMENT | SERIAL | IDENTITY | ||
| 隔离级别 | REPEATABLE READ | READ COMMITTED | READ COMMITTED |
常见坑:MySQL 隐式转换索引失效、NULL 排序默认不一致、collation 冲突。
一句话记忆:跨库 SQL 要验证;隐式转换是 MySQL 头号坑。
相关概念辨析
MySQL vs PostgreSQL vs SQL Server 核心差异
| 维度 | MySQL | PostgreSQL | SQL Server |
|---|---|---|---|
| 默认隔离级别 | REPEATABLE READ | READ COMMITTED | READ COMMITTED |
| NULL 排序 | NULL 最小 | NULL 最大 | NULL 最小 |
| 自增 | AUTO_INCREMENT | SERIAL/IDENTITY | IDENTITY(1,1) |
| 字符串拼接 | CONCAT() | || 或 CONCAT() | + 或 CONCAT() |
| 当前时间 | NOW() | NOW() | GETDATE() |
05.7.5-009 NULL 排序在 MySQL/PG/SQL Server 中的差异?
频率 ★★★ | 难度 基础
一句话结论
MySQL 默认 NULL 最小(排在最前);PostgreSQL 默认 NULL 最大(排在最后);SQL Server 默认 NULL 最小;跨库用 NULLS FIRST/LAST 显式指定。
概念解释
- NULL 不是值,是"未知";排序时各数据库对 NULL 的位置约定不同。
- MySQL:ORDER BY col ASC 时 NULL 在最前。
- PostgreSQL:ORDER BY col ASC 时 NULL 在最后。
- SQL Server:ORDER BY col ASC 时 NULL 在最前。
代码 / 示例
-- MySQL: NULL 在最前
SELECT * FROM t ORDER BY col ASC;
-- PostgreSQL: NULL 在最后
SELECT * FROM t ORDER BY col ASC;
-- 跨库统一写法
SELECT * FROM t ORDER BY col ASC NULLS LAST;
常见误区
- 误区:跨库迁移不用改排序逻辑。正解:同一 SQL 在不同库排序结果不同,业务可能受影响。
追问延伸
- PG 支持 NULLS FIRST/LAST;MySQL 8 也支持;SQL Server 用 CASE WHEN 模拟。
- 迁移时注意报表排序结果可能变化。
待整理清单
- [ ] 三库常用函数对照表(日期/字符串/数字/NULL 处理)
- [ ] EXPLAIN 执行计划字段差异
- [ ] 事务隔离级别默认值差异(MySQL REPEATABLE READ / PG READ COMMITTED)