以下是针对不同数据库系统的 SQL 语法差异总结，按功能分类展示：

一、基础查询

1. 分页查询

— PostgreSQL/TiDB/OceanBase SELECT * FROM TableName LIMIT 10 OFFSET 20; — MySQL SELECT * FROM TableName LIMIT 20, 10; — Oracle SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM TableName ORDER BY column1 ) t WHERE ROWNUM <= 30 ) WHERE rn > 20;

2. 字符串连接

— PostgreSQL/TiDB/OceanBase SELECT column1 || || column2 FROM TableName; — MySQL SELECT CONCAT(column1, , column2) FROM TableName; — Oracle SELECT column1 || || column2 FROM TableName; — 或 SELECT CONCAT(CONCAT(column1, ), column2) FROM TableName;

二、日期函数

1. 当前日期时间

— PostgreSQL/TiDB SELECT NOW(); SELECT CURRENT_TIMESTAMP; — MySQL SELECT NOW(); SELECT CURRENT_TIMESTAMP(); SELECT SYSDATE(); — Oracle SELECT SYSDATE FROM dual; SELECT CURRENT_TIMESTAMP FROM dual; — OceanBase SELECT NOW(); SELECT CURRENT_TIMESTAMP;

2. 日期加减

— PostgreSQL/TiDB/OceanBase SELECT NOW() + INTERVAL 1 day; SELECT NOW() – INTERVAL 1 hour; — MySQL SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR); — Oracle SELECT SYSDATE + 1 FROM dual; SELECT SYSDATE – 1/24 FROM dual;

三、类型转换

1. 字符串转日期

— PostgreSQL/TiDB/OceanBase SELECT CAST(2023-01-01 AS DATE); SELECT 2023-01-01::DATE; — MySQL SELECT CAST(2023-01-01 AS DATE); SELECT STR_TO_DATE(2023-01-01, %Y-%m-%d); — Oracle SELECT TO_DATE(2023-01-01, YYYY-MM-DD) FROM dual;

2. 数字格式化

— PostgreSQL/TiDB/OceanBase SELECT TO_CHAR(1234.56, 9999.99); — MySQL SELECT FORMAT(1234.56, 2); — Oracle SELECT TO_CHAR(1234.56, 9999.99) FROM dual;

四、窗口函数

1. ROW_NUMBER()

— 所有数据库通用语法 SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column2) AS rn FROM TableName;

2. 分页查询（使用窗口函数）

— PostgreSQL/TiDB/OceanBase/MySQL 8.0+ SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY column1) AS rn FROM TableName t ) WHERE rn BETWEEN 21 AND 30; — Oracle SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY column1) AS rn FROM TableName t ) WHERE rn BETWEEN 21 AND 30;

五、DDL 语句

1. 自增列

— PostgreSQL CREATE TABLE TableName ( id SERIAL PRIMARY KEY, name VARCHAR(50) ); — MySQL/TiDB/OceanBase CREATE TABLE TableName ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); — Oracle CREATE TABLE TableName ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(50) );

2. 修改列

— PostgreSQL/TiDB/OceanBase ALTER TABLE TableName ALTER COLUMN column1 TYPE VARCHAR(100); — MySQL ALTER TABLE TableName MODIFY COLUMN column1 VARCHAR(100); — Oracle ALTER TABLE TableName MODIFY column1 VARCHAR2(100);

六、索引操作

1. 创建索引

— PostgreSQL/TiDB/OceanBase CREATE INDEX idx_name ON TableName (column1); — MySQL CREATE INDEX idx_name ON TableName (column1); — Oracle CREATE INDEX idx_name ON TableName (column1);

2. 函数索引

— PostgreSQL CREATE INDEX idx_name ON TableName (LOWER(column1)); — MySQL 8.0+/TiDB/OceanBase CREATE INDEX idx_name ON TableName ((LOWER(column1))); — Oracle CREATE INDEX idx_name ON TableName (LOWER(column1));

七、事务控制

1. 事务语法

— PostgreSQL/MySQL/TiDB/OceanBase START TRANSACTION; — SQL语句 COMMIT; — 或 ROLLBACK; — Oracle SET TRANSACTION; — SQL语句 COMMIT; — 或 ROLLBACK;

八、特殊功能

1. JSON 处理

— PostgreSQL SELECT json_column->>key FROM TableName; — MySQL/TiDB/OceanBase SELECT JSON_EXTRACT(json_column, $.key) FROM TableName; — Oracle 12c+ SELECT json_column.key FROM TableName;

2. 正则表达式

— PostgreSQL/TiDB/OceanBase SELECT * FROM TableName WHERE column1 ~ pattern; — MySQL SELECT * FROM TableName WHERE column1 REGEXP pattern; — Oracle SELECT * FROM TableName WHERE REGEXP_LIKE(column1, pattern);

九、系统函数

1. 获取最后插入ID

— PostgreSQL INSERT INTO TableName (…) VALUES (…) RETURNING id; — MySQL/TiDB/OceanBase INSERT INTO TableName (…) VALUES (…); SELECT LAST_INSERT_ID(); — Oracle INSERT INTO TableName (…) VALUES (…) RETURNING id INTO :variable;

2. 分组合并字符串

— PostgreSQL SELECT string_agg(column1, ,) FROM TableName GROUP BY column2; — MySQL SELECT GROUP_CONCAT(column1 SEPARATOR ,) FROM TableName GROUP BY column2; — Oracle SELECT LISTAGG(column1, ,) WITHIN GROUP (ORDER BY column1) FROM TableName GROUP BY column2; — TiDB/OceanBase SELECT GROUP_CONCAT(column1 SEPARATOR ,) FROM TableName GROUP BY column2;

十、存储过程

1. 基本语法

— PostgreSQL CREATE OR REPLACE FUNCTION func_name() RETURNS void AS $ BEGIN — 代码 END; $ LANGUAGE plpgsql; — MySQL/TiDB/OceanBase DELIMITER // CREATE PROCEDURE proc_name() BEGIN — 代码 END // DELIMITER ; — Oracle CREATE OR REPLACE PROCEDURE proc_name AS BEGIN — 代码 END; /

以上是主要数据库系统在常见 SQL 功能上的语法差异，实际使用时还需考虑各数据库的版本差异。