罗罗编程学习记录
本页内容 展开查看

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)