「数据库语法差异」MySQL / PostgreSQL / SQL Server / Oracle / SQLite 常用语法速查对照

在切换数据库或做数据迁移时，最头疼的不是架构差异，而是那些”看似一样实则不同”的语法细节。本文以 MySQL 为基准，覆盖 PostgreSQL、SQL Server、Oracle、SQLite 五大主流数据库在分页、日期、字符串、JSON、DDL、窗口函数等 20 个维度的语法差异，适合日常速查和跨数据库开发参考。

分页查询
自增主键
日期时间
字符串操作
类型转换
判断与流程控制
MERGE/UPSERT
JSON 处理
DDL 常见差异
查询计划
数据类型映射
字符串引号与转义
正则表达式
多表 JOIN 特有语法
窗口函数差异
存储过程与函数
临时表
Schema/库层级差异
内置函数速查对照表
跨数据库开发建议

一、分页查询

这是最常见的跨库差异，五种数据库写法完全不同。

MySQL / PostgreSQL / SQLite

-- 方式 1：LIMIT + OFFSET（三者通用）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 方式 2：LIMIT 缩写
SELECT * FROM users ORDER BY id LIMIT 20, 10;  -- (MySQL 独有语法)

SQL Server

-- SQL Server 2012+ 支持 OFFSET FETCH（推荐）
SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- 旧版 SQL Server 的 TOP 子查询分页
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM users
) t WHERE rn BETWEEN 21 AND 30;

Oracle

-- Oracle 12c+ 支持 OFFSET FETCH（推荐）
SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle 11g 及以下：ROWNUM 嵌套
SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM (
        SELECT * FROM users ORDER BY id
    ) t WHERE ROWNUM <= 30
) WHERE rn > 20;

分页对照速查

数据库	推荐写法	说明
MySQL	`LIMIT 10 OFFSET 20`	`LIMIT offset, count` 也支持
PostgreSQL	`LIMIT 10 OFFSET 20`	与 MySQL 相同
SQLite	`LIMIT 10 OFFSET 20`	与 MySQL 相同
SQL Server	`OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`	2012+
Oracle	`OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`	12c+

二、自增主键

数据库	语法	获取最后插入 ID
MySQL	`INT AUTO_INCREMENT`	`SELECT LAST_INSERT_ID();`
PostgreSQL	`SERIAL` 或 `INT GENERATED ALWAYS AS IDENTITY`	`INSERT ... RETURNING id;` 或 `SELECT lastval();`
SQL Server	`INT IDENTITY(1,1)`	`SELECT SCOPE_IDENTITY();` 或 `OUTPUT INSERTED.id`
Oracle	`INT GENERATED BY DEFAULT AS IDENTITY`（12c+）或 SEQUENCE	`SELECT seq_name.CURRVAL FROM DUAL;`
SQLite	`INTEGER PRIMARY KEY`（自带自增）	`SELECT last_insert_rowid();`

-- ========== MySQL ==========
CREATE TABLE t1 (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- ========== PostgreSQL ==========
-- 方式 1：传统 SERIAL
CREATE TABLE t1 (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50)
);
-- 方式 2：新标准 IDENTITY（推荐）
CREATE TABLE t1 (
    id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50)
);

-- ========== SQL Server ==========
CREATE TABLE t1 (
    id   INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50)
);

-- ========== Oracle ==========
-- 12c+ IDENTITY
CREATE TABLE t1 (
    id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(50)
);
-- 11g：必须手动创建 SEQUENCE + TRIGGER
CREATE SEQUENCE seq_t1 START WITH 1 INCREMENT BY 1;
CREATE TABLE t1 (
    id   NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);
CREATE OR REPLACE TRIGGER trg_t1_id
BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
    SELECT seq_t1.NEXTVAL INTO :NEW.id FROM DUAL;
END;

-- ========== SQLite ==========
CREATE TABLE t1 (
    id   INTEGER PRIMARY KEY,   -- INTEGER PRIMARY KEY 自动变为自增
    name TEXT
);

三、日期时间

3.1 获取当前时间

需求	MySQL	PostgreSQL	SQL Server	Oracle	SQLite
当前日期+时间	`NOW()`	`NOW()` / `CURRENT_TIMESTAMP`	`GETDATE()`	`SYSDATE`	`datetime('now')`
当前日期	`CURDATE()`	`CURRENT_DATE`	`CAST(GETDATE() AS DATE)`	`TRUNC(SYSDATE)`	`date('now')`
当前时间	`CURTIME()`	`CURRENT_TIME`	`CAST(GETDATE() AS TIME)`	—	`time('now')`

3.2 日期加减

-- ========== MySQL ==========
SELECT NOW() + INTERVAL 7 DAY;              -- 加 7 天
SELECT NOW() - INTERVAL 1 MONTH;            -- 减 1 月
SELECT DATE_ADD(NOW(), INTERVAL 3 HOUR);    -- 加 3 小时

-- ========== PostgreSQL ==========
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';
SELECT NOW() + INTERVAL '3 hours';

-- ========== SQL Server ==========
SELECT DATEADD(DAY, 7, GETDATE());           -- 加 7 天
SELECT DATEADD(MONTH, -1, GETDATE());        -- 减 1 月
SELECT DATEADD(HOUR, 3, GETDATE());          -- 加 3 小时

-- ========== Oracle ==========
SELECT SYSDATE + 7 FROM DUAL;                -- 加 7 天
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;    -- 减 1 月
SELECT SYSDATE + 3/24 FROM DUAL;             -- 加 3 小时

-- ========== SQLite ==========
SELECT datetime('now', '+7 days');
SELECT datetime('now', '-1 month');
SELECT datetime('now', '+3 hours');

3.3 日期差值

-- MySQL / SQLite:
SELECT DATEDIFF('2024-12-31', '2024-01-01');
-- → 365（SQLite 用 julianday 更精确）

-- PostgreSQL:
SELECT '2024-12-31'::DATE - '2024-01-01'::DATE;
-- → 365

-- SQL Server:
SELECT DATEDIFF(DAY, '2024-01-01', '2024-12-31');
-- → 365

-- Oracle:
SELECT TO_DATE('2024-12-31','YYYY-MM-DD') - TO_DATE('2024-01-01','YYYY-MM-DD') FROM DUAL;
-- → 365

3.4 格式化日期为字符串

-- MySQL:
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');   -- → 2024-01-15 14:30:00

-- PostgreSQL:
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');    -- → 2024-01-15 14:30:00

-- SQL Server:
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss');   -- → 2024-01-15 14:30:00
-- 或 CONVERT(VARCHAR, GETDATE(), 120)

-- Oracle:
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- SQLite:
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');

四、字符串操作

4.1 拼接字符串

-- MySQL:
SELECT CONCAT('Hello', ' ', 'World');               -- → Hello World
SELECT CONCAT_WS('-', '2024', '01', '15');           -- → 2024-01-15

-- PostgreSQL:
SELECT 'Hello' || ' ' || 'World';                    -- → Hello World
SELECT CONCAT('Hello', ' ', 'World');                -- 也支持 CONCAT

-- SQL Server:
SELECT 'Hello' + ' ' + 'World';                      -- → Hello World
SELECT CONCAT('Hello', ' ', 'World');                -- SQL Server 2012+ 也支持

-- Oracle:
SELECT 'Hello' || ' ' || 'World' FROM DUAL;          -- → Hello World
SELECT CONCAT('Hello', CONCAT(' ', 'World')) FROM DUAL;  -- 只支持 2 个参数

-- SQLite:
SELECT 'Hello' || ' ' || 'World';                    -- → Hello World

4.2 子字符串

-- MySQL / PostgreSQL / SQLite:
SELECT SUBSTRING('Hello World', 1, 5);    -- → Hello（注意位置从 1 开始）

-- SQL Server:
SELECT SUBSTRING('Hello World', 1, 5);    -- 同上

-- Oracle:
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;  -- → Hello

4.3 字符串长度

-- MySQL:
SELECT CHAR_LENGTH('你好世界');   -- → 4（字符数）
SELECT LENGTH('你好世界');         -- → 12（字节数，UTF-8 中每个中文 3 字节）

-- PostgreSQL:
SELECT CHAR_LENGTH('你好世界');   -- → 4
SELECT LENGTH('你好世界');         -- → 4（PostgreSQL 中 LENGTH 也是字符数）

-- SQL Server:
SELECT LEN('你好世界');            -- → 4（字符数）
SELECT DATALENGTH('你好世界');     -- → 12（字节数，NCHAR/NVARCHAR 中每个 2 字节）

-- Oracle:
SELECT LENGTH('你好世界') FROM DUAL;         -- → 4（字符数）
SELECT LENGTHB('你好世界') FROM DUAL;        -- → 12（字节数）

-- SQLite:
SELECT LENGTH('你好世界');         -- → 4（字符数）

4.4 大小写转换

-- 大写（五家通用）:
SELECT UPPER('hello');            -- → HELLO

-- 小写（五家通用）:
SELECT LOWER('HELLO');            -- → hello

-- 首字母大写（各不同）:
-- PostgreSQL: SELECT INITCAP('hello world');    -- → Hello World
-- SQL Server: 无内置，需自定义函数
-- Oracle:     SELECT INITCAP('hello world') FROM DUAL;
-- MySQL:      无内置
-- SQLite:     无内置

4.5 搜索与替换

-- 子串位置
-- MySQL:    SELECT INSTR('Hello World', 'World');        -- → 7
--           SELECT LOCATE('World', 'Hello World');       -- → 7
-- PostgreSQL / Oracle / SQLite: SELECT INSTR(...) （Oracle） 或 POSITION('World' IN 'Hello World')
-- SQL Server: SELECT CHARINDEX('World', 'Hello World');  -- → 7

-- 替换
-- 五家通用:
SELECT REPLACE('Hello World', 'World', 'SQL');  -- → Hello SQL

五、类型转换

-- ========== MySQL ==========
SELECT CAST('123' AS INT);
SELECT CONVERT('123', INT);
SELECT CAST('2024-01-01' AS DATE);

-- ========== PostgreSQL ==========
SELECT CAST('123' AS INTEGER);
SELECT '123'::INTEGER;                      -- :: 后置强转（PostgreSQL 特有）
SELECT CAST('2024-01-01' AS DATE);

-- ========== SQL Server ==========
SELECT CAST('123' AS INT);
SELECT CONVERT(INT, '123');                 -- CONVERT 函数（特有）
SELECT CONVERT(DATE, '2024-01-01', 120);    -- 带格式码转换

-- ========== Oracle ==========
SELECT CAST('123' AS NUMBER) FROM DUAL;
SELECT TO_NUMBER('123') FROM DUAL;          -- Oracle 常用 TO_* 系列
SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD') FROM DUAL;

-- ========== SQLite ==========
SELECT CAST('123' AS INTEGER);
-- SQLite 类型系统宽松，很多情况下自动转换

六、判断与流程控制

6.1 IF / CASE

-- CASE 表达式（五家通用）:
SELECT 
    name,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 60 THEN 'C'
        ELSE 'D'
    END AS grade
FROM students;

-- ========== MySQL 专属: IF() 函数 ==========
SELECT IF(score >= 60, '及格', '不及格') FROM students;

-- ========== PostgreSQL 专属: 无 IF()，用 CASE ==========

-- ========== SQL Server 专属: IIF() 函数 ==========
SELECT IIF(score >= 60, '及格', '不及格') FROM students;

-- ========== Oracle 专属: DECODE() 函数 ==========
SELECT DECODE(status, 1, '激活', 2, '禁用', '未知') FROM users;
-- (类似 switch-case，比 CASE 简洁)

-- ========== SQLite 专属: IIF() ==========
SELECT IIF(score >= 60, '及格', '不及格') FROM students;

6.2 NULL 处理

-- COALESCE（返回第一个非 NULL 值，五家通用）:
SELECT COALESCE(nickname, username, '匿名') FROM users;

-- NULLIF（相等返回 NULL，五家通用）:
SELECT NULLIF(0, 0);   -- → NULL
SELECT NULLIF(1, 0);   -- → 1

-- MySQL / SQLite 专属: IFNULL()
SELECT IFNULL(nickname, '匿名') FROM users;

-- PostgreSQL: 无 IFNULL，用 COALESCE

-- SQL Server 专属: ISNULL()
SELECT ISNULL(nickname, '匿名') FROM users;

-- Oracle 专属: NVL()
SELECT NVL(nickname, '匿名') FROM users;
-- Oracle 还有 NVL2(): NVL2(expr, not_null_val, null_val)

七、MERGE/UPSERT

“存在则更新，不存在则插入” —— 这是跨库差异最大的语法之一。

MySQL

-- ON DUPLICATE KEY UPDATE（需要唯一键冲突）
INSERT INTO users (id, name, email)
VALUES (1, '张三', 'zhangsan@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

-- 或者 REPLACE（先删后插，慎用——会丢失未指定的列值）
REPLACE INTO users (id, name, email) VALUES (1, '张三', 'zhangsan@example.com');

PostgreSQL

-- ON CONFLICT DO UPDATE（推荐，PostgreSQL 9.5+）
INSERT INTO users (id, name, email)
VALUES (1, '张三', 'zhangsan@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;

-- ON CONFLICT DO NOTHING（存在则忽略）
INSERT INTO users (id, name, email)
VALUES (1, '张三', 'zhangsan@example.com')
ON CONFLICT (id) DO NOTHING;

SQL Server

-- MERGE 语句（SQL Server 2008+）
MERGE users AS target
USING (VALUES (1, '张三', 'zhangsan@example.com')) AS source (id, name, email)
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email) VALUES (source.id, source.name, source.email);

Oracle

-- MERGE 语句（Oracle 9i+）
MERGE INTO users t
USING (SELECT 1 AS id, '张三' AS name, 'zhangsan@example.com' AS email FROM DUAL) s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.email = s.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email) VALUES (s.id, s.name, s.email);

SQLite

-- ON CONFLICT（SQLite 3.24+）
INSERT INTO users (id, name, email)
VALUES (1, '张三', 'zhangsan@example.com')
ON CONFLICT(id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;

-- 旧版用 INSERT OR REPLACE（注意：会删除旧行再插入）
INSERT OR REPLACE INTO users (id, name, email) VALUES (1, '张三', 'zhangsan@example.com');

UPSERT 对照速查

数据库	UPSERT 语法	版本要求
MySQL	`INSERT ... ON DUPLICATE KEY UPDATE`	4.1+
PostgreSQL	`INSERT ... ON CONFLICT DO UPDATE`	9.5+
SQL Server	`MERGE` 语句	2008+
Oracle	`MERGE` 语句	9i+
SQLite	`INSERT ... ON CONFLICT DO UPDATE`	3.24+

八、JSON 处理

8.1 JSON 提取值

-- ========== MySQL (5.7+) ==========
-- 路径语法: $.key
SELECT JSON_EXTRACT('{"name":"张三","age":25}', '$.name');
-- 或 -> 简写:
SELECT '{"name":"张三","age":25}'->'$.name';
-- 去除引号:
SELECT '{"name":"张三","age":25}'->>'$.name';  -- → 张三（无引号）

-- ========== PostgreSQL (9.3+) ==========
-- 路径语法: -> 和 ->>
SELECT '{"name":"张三","age":25}'::json->'name';     -- → "张三"（JSONB 更高效）
SELECT '{"name":"张三","age":25}'::jsonb->>'name';   -- → 张三

-- ========== SQL Server (2016+) ==========
SELECT JSON_VALUE('{"name":"张三","age":25}', '$.name');  -- → 张三
SELECT JSON_QUERY('{"items":[1,2,3]}', '$.items');       -- → [1,2,3]

-- ========== Oracle (12c+) ==========
SELECT JSON_VALUE('{"name":"张三","age":25}', '$.name') FROM DUAL;
SELECT JSON_QUERY('{"items":[1,2,3]}', '$.items') FROM DUAL;

-- ========== SQLite (3.9+) ==========
SELECT json_extract('{"name":"张三","age":25}', '$.name');
-- 或 -> 简写:
SELECT '{"name":"张三","age":25}'->'$.name';

8.2 JSON 数组展开

-- MySQL 8.0+: JSON_TABLE()
SELECT * FROM JSON_TABLE(
    '[{"name":"张三"},{"name":"李四"}]',
    '$[*]' COLUMNS (name VARCHAR(50) PATH '$.name')
) AS jt;

-- PostgreSQL: jsonb_array_elements()
SELECT value->>'name' AS name
FROM jsonb_array_elements('[{"name":"张三"},{"name":"李四"}]');

-- SQL Server: OPENJSON()
SELECT * FROM OPENJSON('[{"name":"张三"},{"name":"李四"}]')
WITH (name NVARCHAR(50) '$.name');

-- Oracle: JSON_TABLE()
SELECT * FROM JSON_TABLE(
    '[{"name":"张三"},{"name":"李四"}]',
    '$[*]' COLUMNS (name VARCHAR2(50) PATH '$.name')
);

-- SQLite: json_each()
SELECT json_extract(value, '$.name')
FROM json_each('[{"name":"张三"},{"name":"李四"}]');

九、DDL 常见差异

9.1 修改列

-- MySQL:
ALTER TABLE users MODIFY COLUMN name VARCHAR(100) NOT NULL;
ALTER TABLE users CHANGE COLUMN old_name new_name VARCHAR(100);

-- PostgreSQL:
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(100);
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users RENAME COLUMN old_name TO new_name;

-- SQL Server:
ALTER TABLE users ALTER COLUMN name VARCHAR(100) NOT NULL;
EXEC sp_rename 'users.old_name', 'new_name', 'COLUMN';

-- Oracle:
ALTER TABLE users MODIFY name VARCHAR2(100) NOT NULL;
ALTER TABLE users RENAME COLUMN old_name TO new_name;

-- SQLite:
-- SQLite 不支持直接修改列，需要重建表（3.35+ 支持 ALTER TABLE ... RENAME COLUMN）
ALTER TABLE users RENAME COLUMN old_name TO new_name;

9.2 添加/删除列

-- 添加列（五家基本相同）:
ALTER TABLE users ADD COLUMN age INT;

-- 删除列:
-- MySQL:      ALTER TABLE users DROP COLUMN age;
-- PostgreSQL: ALTER TABLE users DROP COLUMN age;
-- SQL Server: ALTER TABLE users DROP COLUMN age;
-- Oracle:     ALTER TABLE users DROP COLUMN age;
-- SQLite:     不支持 DROP COLUMN（3.35+ 支持）
ALTER TABLE users DROP COLUMN age;

9.3 截断表

-- MySQL / PostgreSQL / SQL Server / Oracle / SQLite:
TRUNCATE TABLE users;

-- 差异点：
-- MySQL:      释放存储空间，重置 AUTO_INCREMENT
-- PostgreSQL: 默认不重置序列，需加 RESTART IDENTITY
TRUNCATE TABLE users RESTART IDENTITY;
-- Oracle:     不可回滚，不触发 DELETE 触发器
-- SQL Server: 需要 ALTER 权限

9.4 创建索引

-- 普通索引（五家通用）:
CREATE INDEX idx_name ON users(name);

-- 唯一索引（五家通用）:
CREATE UNIQUE INDEX idx_email ON users(email);

-- 部分/条件索引（MySQL 不支持，PostgreSQL / SQL Server / Oracle / SQLite 支持）:
CREATE INDEX idx_active_users ON users(email) WHERE status = 1;  -- PostgreSQL / SQLite
CREATE INDEX idx_active_users ON users(email) WHERE status = 1;  -- SQL Server: 用 Filtered Index

9.5 注释

-- MySQL: COMMENT 关键字
CREATE TABLE t1 (
    id   INT COMMENT '主键ID',
    name VARCHAR(50) COMMENT '用户名'
) COMMENT='用户表';

-- PostgreSQL: COMMENT ON 语法
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.name IS '用户名';

-- SQL Server: 扩展属性
EXEC sp_addextendedproperty 'MS_Description', '用户表', 'SCHEMA', 'dbo', 'TABLE', 'users';

-- Oracle: COMMENT ON 语法（与 PostgreSQL 类似）
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.name IS '用户名';

-- SQLite: 不支持注释

十、查询计划

-- MySQL:
EXPLAIN SELECT * FROM users WHERE name = '张三';
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';   -- 8.0.18+，实际执行并统计

-- PostgreSQL:
EXPLAIN SELECT * FROM users WHERE name = '张三';
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';   -- 实际执行并统计
-- PostgreSQL 的输出信息最详细

-- SQL Server:
SET SHOWPLAN_XML ON;   -- 图形化执行计划（SSMS 中直接看）
-- 或
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Oracle:
EXPLAIN PLAN FOR SELECT * FROM users WHERE name = '张三';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- SQLite:
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = '张三';

十一、数据类型映射

场景	MySQL	PostgreSQL	SQL Server	Oracle	SQLite
自增整数	`INT AUTO_INCREMENT`	`SERIAL` / `INT GENERATED AS IDENTITY`	`INT IDENTITY`	`NUMBER GENERATED AS IDENTITY`	`INTEGER PRIMARY KEY`
变长字符	`VARCHAR(N)`	`VARCHAR(N)`	`NVARCHAR(N)` (Unicode)	`VARCHAR2(N)`	`TEXT`
定长字符	`CHAR(N)`	`CHAR(N)`	`NCHAR(N)`	`CHAR(N)`	—
长文本	`TEXT` / `MEDIUMTEXT` / `LONGTEXT`	`TEXT`	`NVARCHAR(MAX)`	`CLOB`	`TEXT`
整数	`INT`	`INTEGER`	`INT`	`NUMBER`	`INTEGER`
小数	`DECIMAL(P,S)`	`DECIMAL(P,S)`	`DECIMAL(P,S)`	`NUMBER(P,S)`	`REAL` / `NUMERIC`
布尔值	`TINYINT(1)` / `BOOLEAN` (8.0+)	`BOOLEAN`	`BIT`	`NUMBER(1)`	`INTEGER` (0/1)
日期时间	`DATETIME`	`TIMESTAMP`	`DATETIME2`	`DATE` / `TIMESTAMP`	`TEXT` (ISO 8601)
二进制	`BLOB`	`BYTEA`	`VARBINARY(MAX)`	`BLOB`	`BLOB`
JSON	`JSON`	`JSON` / `JSONB`	`NVARCHAR(MAX)` (ISJSON 约束)	`JSON` / `VARCHAR2`	`TEXT`

十二、字符串引号与转义

12.1 引号规则

数据库	字符串	标识符引用	别名引用
MySQL	`'单引号'` 或 `"双引号"`（ANSI 模式）	`反引号`	`反引号` 或不加
PostgreSQL	`'单引号'`	`"双引号"`	`"双引号"` 或不加
SQL Server	`'单引号'`	`[方括号]` 或 `"双引号"`	`[方括号]` 或 `"双引号"`
Oracle	`'单引号'`	`"双引号"`	`"双引号"` 或不加
SQLite	`'单引号'`	`"双引号"` 或 `反引号` 或 `[方括号]`	同上

12.2 转义

-- MySQL:
SELECT 'It''s fine';                        -- 两个单引号转义
SELECT "He said \"hello\"";                 -- 反斜杠
SELECT 'It\'s fine';                        -- 也支持反斜杠（非 SQL 标准）

-- PostgreSQL:
SELECT 'It''s fine';                        -- 两个单引号（标准做法）
SELECT $$It's "fine"$$;                     -- Dollar-sign quoting（不用转义）

-- SQL Server:
SELECT 'It''s fine';                        -- 两个单引号

-- Oracle:
SELECT 'It''s fine' FROM DUAL;              -- 两个单引号
SELECT q'[It's "fine"]' FROM DUAL;          -- Q 引用（Oracle 10g+）

-- SQLite:
SELECT 'It''s fine';                        -- 两个单引号

十三、正则表达式

-- MySQL (REGEXP):
SELECT * FROM users WHERE email REGEXP '^[a-z]+@[a-z]+\\.com$';
SELECT * FROM users WHERE name REGEXP '张(三|四)';       -- 正则匹配
SELECT REGEXP_REPLACE('abc123', '[0-9]+', '');           -- 替换（MySQL 8.0+）

-- PostgreSQL (~ 运算符):
SELECT * FROM users WHERE email ~ '^[a-z]+@[a-z]+\.com$';
SELECT * FROM users WHERE name ~ '张(三|四)';
SELECT REGEXP_REPLACE('abc123', '[0-9]+', '', 'g');

-- SQL Server: 无原生正则，用 LIKE 或 CLR 扩展
-- LIKE 支持有限通配符: % _ [a-z] [^a-z]
SELECT * FROM users WHERE name LIKE '[张李]%';

-- Oracle (REGEXP_LIKE):
SELECT * FROM users WHERE REGEXP_LIKE(email, '^[a-z]+@[a-z]+\.com$');
SELECT REGEXP_REPLACE('abc123', '[0-9]+', '') FROM DUAL;

-- SQLite (REGEXP 默认未实现，需自定义函数):
-- 需在应用层注册 regexp()
SELECT * FROM users WHERE name REGEXP '张(三|四)';  -- 需要先注册

十四、多表 JOIN 特有语法

14.1 MySQL 独有：STRAIGHT_JOIN

1
2
3

-- 强制按指定顺序 JOIN（跳过优化器的 JOIN 顺序优化）
SELECT * FROM small_table
STRAIGHT_JOIN large_table ON small_table.id = large_table.small_id;

14.2 SQL Server 独有：APPLY

-- CROSS APPLY：类似 INNER JOIN 子查询，但可以引用外部列
SELECT u.name, t.order_count
FROM users u
CROSS APPLY (
    SELECT COUNT(*) AS order_count
    FROM orders o WHERE o.user_id = u.id
) t;

-- OUTER APPLY：类似 LEFT JOIN 子查询
SELECT u.name, t.order_count
FROM users u
OUTER APPLY (
    SELECT COUNT(*) AS order_count
    FROM orders o WHERE o.user_id = u.id
) t;

PostgreSQL 用 JOIN LATERAL 实现相同功能，MySQL 8.0+ 也支持 LATERAL。

14.3 Oracle 独有：(+) 外连接

-- Oracle 传统外连接写法（不推荐，但遗留代码中常见）
SELECT u.name, o.order_id
FROM users u, orders o
WHERE u.id = o.user_id(+);  -- (+) = 右表可为空（LEFT JOIN 效果）

14.4 NATURAL JOIN 语义差异

-- MySQL / PostgreSQL / SQLite：基于同名列自动关联
SELECT * FROM users NATURAL JOIN orders;
-- 自动找 users 和 orders 中同名的列做等值 JOIN

-- 注意：SQL Server 不直接支持 NATURAL JOIN

十五、窗口函数差异

窗口函数（OVER 子句）在五大数据库中基本统一，但仍有以下差异：

功能	MySQL	PostgreSQL	SQL Server	Oracle	SQLite
窗口函数	8.0+	✅	2005+	✅	3.25+
ROW_NUMBER	✅	✅	✅	✅	✅
RANK / DENSE_RANK	✅	✅	✅	✅	✅
LAG / LEAD	✅	✅	✅	✅	✅
SUM/AVG OVER	✅	✅	✅	✅	✅
GROUPS 窗口	❌	✅	❌	✅	❌
RANGE BETWEEN	✅	✅	✅	✅	❌
EXCLUDE TIES	❌	✅	❌	✅	❌
NTH_VALUE	✅	✅	✅	✅	✅
命名窗口	✅	✅	✅	✅	✅

-- 命名窗口（大多数数据库支持）:
SELECT 
    name,
    salary,
    dept_id,
    ROW_NUMBER() OVER w AS rn,
    SUM(salary) OVER w AS running_total
FROM employees
WINDOW w AS (PARTITION BY dept_id ORDER BY salary);
-- 注意：SQL Server 不支持 WINDOW 子句，需要在每个 OVER 中重复定义

十六、存储过程与函数

16.1 简单存储过程对比

-- ========== MySQL ==========
DELIMITER $$
CREATE PROCEDURE get_users_by_status(IN p_status INT)
BEGIN
    SELECT * FROM users WHERE status = p_status;
END$$
DELIMITER ;
CALL get_users_by_status(1);

-- ========== PostgreSQL ==========
CREATE OR REPLACE FUNCTION get_users_by_status(p_status INT)
RETURNS TABLE(id INT, name VARCHAR, email VARCHAR) AS $$
BEGIN
    RETURN QUERY SELECT u.id, u.name, u.email FROM users u WHERE u.status = p_status;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_users_by_status(1);

-- ========== SQL Server ==========
CREATE PROCEDURE get_users_by_status @p_status INT
AS
BEGIN
    SELECT * FROM users WHERE status = @p_status;
END;
EXEC get_users_by_status 1;

-- ========== Oracle ==========
CREATE OR REPLACE PROCEDURE get_users_by_status(p_status IN NUMBER) IS
BEGIN
    FOR rec IN (SELECT * FROM users WHERE status = p_status) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.name);
    END LOOP;
END;
EXEC get_users_by_status(1);

-- ========== SQLite ==========
-- SQLite 不支持存储过程（嵌入式数据库特性）

16.2 语言选择

数据库	过程语言
MySQL	SQL/PSM（默认），也支持 JavaScript（8.0+）
PostgreSQL	PL/pgSQL（默认）、PL/Python、PL/Perl、PL/V8(JavaScript)、PL/Java、PL/R 等
SQL Server	T-SQL（默认），也支持 CLR 语言（C#、VB.NET）
Oracle	PL/SQL（默认），也支持 Java
SQLite	不支持

十七、临时表

-- ========== MySQL ==========
-- 会话级临时表（连接断开自动删除）
CREATE TEMPORARY TABLE tmp_users AS
SELECT * FROM users WHERE status = 1;

-- ========== PostgreSQL ==========
-- 会话级临时表
CREATE TEMPORARY TABLE tmp_users AS
SELECT * FROM users WHERE status = 1;
-- 或事务级临时表（事务结束自动删除）
CREATE TEMPORARY TABLE tmp_users ON COMMIT DROP AS
SELECT * FROM users WHERE status = 1;

-- ========== SQL Server ==========
-- 本地临时表（# 开头，会话级）
SELECT * INTO #tmp_users FROM users WHERE status = 1;
-- 全局临时表（## 开头，所有会话可见）
SELECT * INTO ##tmp_users FROM users WHERE status = 1;

-- ========== Oracle ==========
-- 全局临时表（结构持久，数据会话/事务级）
CREATE GLOBAL TEMPORARY TABLE tmp_users (
    id NUMBER, name VARCHAR2(50)
) ON COMMIT DELETE ROWS;  -- 或 ON COMMIT PRESERVE ROWS
INSERT INTO tmp_users SELECT id, name FROM users WHERE status = 1;

-- ========== SQLite ==========
-- 临时表（连接关闭删除）
CREATE TEMPORARY TABLE tmp_users AS
SELECT * FROM users WHERE status = 1;

十八、Schema/库层级差异

-- MySQL: database = schema（二者概念等价）
CREATE DATABASE my_app;
USE my_app;
CREATE TABLE users (...);

-- PostgreSQL: database > schema（一个 database 可以包含多个 schema）
CREATE DATABASE my_app;
\c my_app
CREATE SCHEMA app_data;
CREATE TABLE app_data.users (...);
-- 默认 schema 是 public，可通过 search_path 控制

-- SQL Server: database > schema
CREATE DATABASE my_app;
USE my_app;
CREATE SCHEMA app_data;
CREATE TABLE app_data.users (...);
-- 默认 schema 是 dbo

-- Oracle: instance > schema（schema = 用户，一个用户一个 schema）
CREATE USER app_data IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO app_data;
CREATE TABLE app_data.users (...);

-- SQLite: 单文件 = 一个数据库，无 schema 概念
-- 但可以通过 ATTACH 同时使用多个数据库文件
ATTACH DATABASE 'other.db' AS other;
SELECT * FROM main.users JOIN other.logs ON ...;

十九、内置函数速查对照表

以下是日常开发中最常用函数的跨库对照，建议收藏。

功能	MySQL	PostgreSQL	SQL Server	Oracle	SQLite
获取当前时间	`NOW()`	`NOW()`	`GETDATE()`	`SYSDATE`	`datetime('now')`
转大写	`UPPER()`	`UPPER()`	`UPPER()`	`UPPER()`	`UPPER()`
转小写	`LOWER()`	`LOWER()`	`LOWER()`	`LOWER()`	`LOWER()`
字符串拼接	`CONCAT()`	`\|\|` / `CONCAT()`	`+` / `CONCAT()`	`\|\|`	`\|\|`
子串	`SUBSTRING()`	`SUBSTRING()`	`SUBSTRING()`	`SUBSTR()`	`SUBSTR()`
字符长度	`CHAR_LENGTH()`	`CHAR_LENGTH()`	`LEN()`	`LENGTH()`	`LENGTH()`
去空格	`TRIM()`	`TRIM()`	`TRIM()` / `LTRIM()` / `RTRIM()`	`TRIM()`	`TRIM()`
四舍五入	`ROUND()`	`ROUND()`	`ROUND()`	`ROUND()`	`ROUND()`
取绝对值	`ABS()`	`ABS()`	`ABS()`	`ABS()`	`ABS()`
取模	`MOD()` / `%`	`MOD()` / `%`	`%`	`MOD()`	`%`
随机数	`RAND()`	`RANDOM()`	`RAND()`	`DBMS_RANDOM.VALUE`	`RANDOM()`
字符串替换	`REPLACE()`	`REPLACE()`	`REPLACE()`	`REPLACE()`	`REPLACE()`
MD5	`MD5()`	`MD5()` (pgcrypto)	`HASHBYTES('MD5',...)`	`DBMS_OBFUSCATION_TOOLKIT`	—
分组拼接	`GROUP_CONCAT()`	`STRING_AGG()`	`STRING_AGG()`	`LISTAGG()`	`GROUP_CONCAT()`
首行值	—	`FIRST_VALUE()`	`FIRST_VALUE()`	`FIRST_VALUE()`	`FIRST_VALUE()`
空值默认	`IFNULL()`	`COALESCE()`	`ISNULL()`	`NVL()`	`IFNULL()`

分组拼接对比（这个差异常用）

-- MySQL:
SELECT dept_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
FROM employees GROUP BY dept_id;

-- PostgreSQL:
SELECT dept_id, STRING_AGG(name, ', ' ORDER BY name)
FROM employees GROUP BY dept_id;

-- SQL Server:
SELECT dept_id, STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name)
FROM employees GROUP BY dept_id;

-- Oracle:
SELECT dept_id, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)
FROM employees GROUP BY dept_id;

-- SQLite:
SELECT dept_id, GROUP_CONCAT(name, ', ')
FROM employees GROUP BY dept_id;

二十、跨数据库开发建议

20.1 能统一的写法

以下语法在五大数据库中都通用，优先使用：

SELECT ... FROM ... WHERE ... JOIN ... ON ... GROUP BY ... HAVING ... ORDER BY
CASE WHEN ... THEN ... ELSE ... END
COALESCE()、NULLIF()
CAST(expr AS type)
ROW_NUMBER() OVER (...)
INNER JOIN / LEFT JOIN
CREATE INDEX、DROP TABLE、TRUNCATE TABLE
EXISTS / IN 子查询

20.2 必须隔离的写法

以下需要为不同数据库写不同的 SQL，建议放在 ORM 层或独立的 SQL 模板中：

分页查询（LIMIT vs TOP vs ROWNUM vs FETCH）
自增主键（AUTO_INCREMENT vs SERIAL vs IDENTITY）
UPSERT（ON DUPLICATE KEY vs ON CONFLICT vs MERGE）
获取当前时间（NOW() vs GETDATE() vs SYSDATE）
布尔值（TINYINT(1) vs BOOLEAN vs BIT）
限制返回行数（LIMIT vs FETCH FIRST vs TOP）
标识符引用符（` vs " vs []）

20.3 迁移时的常见坑

隐式类型转换：MySQL 宽松，Oracle/PostgreSQL 严格，迁移后很多查询会报错
NULL 排序：MySQL 默认 NULL 最小（ASC 时在前），Oracle 默认 NULL 最大（ASC 时在后），PostgreSQL 用 NULLS FIRST/LAST 显式控制
空字符串 vs NULL：Oracle 将空字符串视为 NULL，MySQL 区分二者
标识符大小写：MySQL 区分取决于 OS，PostgreSQL 默认全转小写，Oracle 默认全转大写
DML 提交：MySQL 默认自动提交，Oracle 必须手动 COMMIT，PostgreSQL 自动提交
索引名全局性：MySQL 索引名在表内唯一，其他数据库在 Schema 内唯一

附录：线上快速测试各数据库

# MySQL
docker run -d --name mysql-test -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:8

# PostgreSQL
docker run -d --name pg-test -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:16

# SQL Server
docker run -d --name mssql-test -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourPass123' -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

# Oracle (需要较大内存)
docker run -d --name oracle-test -e ORACLE_PASSWORD=oracle -p 1521:1521 gvenzl/oracle-xe:21

# SQLite（无需 Docker）
sqlite3 test.db

本文覆盖了五大主流关系型数据库在日常开发中最常见的语法差异。建议收藏作为跨数据库开发的速查手册，在技术选型、系统迁移、多数据库适配等场景下快速检索。

目录

一、分页查询

MySQL / PostgreSQL / SQLite

SQL Server

Oracle

分页对照速查

二、自增主键

三、日期时间

3.1 获取当前时间

3.2 日期加减

3.3 日期差值

3.4 格式化日期为字符串

四、字符串操作

4.1 拼接字符串

4.2 子字符串

4.3 字符串长度

4.4 大小写转换

4.5 搜索与替换

五、类型转换

六、判断与流程控制

6.1 IF / CASE

6.2 NULL 处理

七、MERGE/UPSERT

MySQL

PostgreSQL

SQL Server

Oracle

SQLite

UPSERT 对照速查

八、JSON 处理

8.1 JSON 提取值

8.2 JSON 数组展开

九、DDL 常见差异

9.1 修改列

9.2 添加/删除列

9.3 截断表

9.4 创建索引

9.5 注释

十、查询计划

十一、数据类型映射

十二、字符串引号与转义

12.1 引号规则

12.2 转义

十三、正则表达式

十四、多表 JOIN 特有语法

14.1 MySQL 独有：STRAIGHT_JOIN

14.2 SQL Server 独有：APPLY

14.3 Oracle 独有：(+) 外连接

14.4 NATURAL JOIN 语义差异

十五、窗口函数差异

十六、存储过程与函数

16.1 简单存储过程对比

16.2 语言选择

十七、临时表

十八、Schema/库层级差异

十九、内置函数速查对照表

分组拼接对比（这个差异常用）

二十、跨数据库开发建议

20.1 能统一的写法

20.2 必须隔离的写法

20.3 迁移时的常见坑

附录：线上快速测试各数据库