PostgreSQL 与 MySQL 语法差异对比总结

以下从 数据类型、运算符、SQL函数、分页语法、事务控制、高级功能 等维度，深入对比 PostgreSQL 与 MySQL 的语法差异，并提供代码示例。

1. 数据类型差异

1.1 整数类型

• MySQL：TINYINT、MEDIUMINT、INT
• PostgreSQL：SMALLINT、INT、BIGINT

示例：

-- MySQL
CREATE TABLE example_mysql (
    small_int TINYINT,
    medium_int MEDIUMINT,
    int_column INT
);

-- PostgreSQL
CREATE TABLE example_pg (
    small_int SMALLINT,
    int_column INT,
    big_int_column BIGINT
);

1.2 文本类型

• MySQL：TEXT 最大支持 64KB，VARCHAR 最大 65535 字节。
• PostgreSQL：TEXT 无长度限制，VARCHAR(n) 实际等价于 TEXT。

示例：

-- MySQL
CREATE TABLE example_mysql (
    description TEXT,
    short_text VARCHAR(255)
);

-- PostgreSQL
CREATE TABLE example_pg (
    description TEXT,
    short_text TEXT  -- VARCHAR(255) 在 PostgreSQL 中等价于 TEXT
);

1.3 字符串连接

• MySQL：支持 CONCAT() 或 +（需开启 PIPES_AS_CONCAT 模式）
• PostgreSQL：仅支持 ||

示例：

-- MySQL
SELECT CONCAT('Hello', ' ', 'World');
-- 或
SELECT 'Hello' + ' ' + 'World' FROM dual;

-- PostgreSQL
SELECT 'Hello' || ' ' || 'World';

1.4 数组类型

• MySQL：不支持原生数组，需用 JSON 或字符串模拟。
• PostgreSQL：支持原生数组类型。

示例：

-- MySQL (模拟数组)
CREATE TABLE example_mysql (
    tags JSON
);
INSERT INTO example_mysql (tags) VALUES ('["python", "sql"]');

-- PostgreSQL
CREATE TABLE example_pg (
    tags TEXT[]
);
INSERT INTO example_pg (tags) VALUES (ARRAY['python', 'sql']);

2. SQL 函数差异

2.1 获取当前时间

• MySQL：NOW() 或 SYSDATE()
• PostgreSQL：CURRENT_TIMESTAMP 或 CURRENT_DATE

示例：

-- MySQL
SELECT NOW();

-- PostgreSQL
SELECT CURRENT_TIMESTAMP;

2.2 日期时间计算

• MySQL：使用 DATE_ADD()、DATE_SUB()
• PostgreSQL：使用 INTERVAL

示例：

-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

-- PostgreSQL
SELECT NOW() + INTERVAL '1 day';

2.3 分页语法

• MySQL：LIMIT offset, count
• PostgreSQL：LIMIT count OFFSET offset

示例：

-- MySQL (第 2 页，每页 10 条)
SELECT * FROM users LIMIT 10, 10;

-- PostgreSQL (第 2 页，每页 10 条)
SELECT * FROM users LIMIT 10 OFFSET 10;

3. 自增主键定义

3.1 MySQL

• 使用 AUTO_INCREMENT 属性。

示例：

-- MySQL
CREATE TABLE example_mysql (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

3.2 PostgreSQL

• 使用 SERIAL 或 IDENTITY 列。

示例：

-- PostgreSQL
CREATE TABLE example_pg (
    id SERIAL PRIMARY KEY,
    name TEXT
);

4. 事务控制

4.1 事务语法

两者均支持 BEGIN、COMMIT、ROLLBACK，但保存点语法略有差异。

示例：

-- MySQL
START TRANSACTION;
SAVEPOINT my_savepoint;
ROLLBACK TO my_savepoint;

-- PostgreSQL
BEGIN;
SAVEPOINT my_savepoint;
ROLLBACK TO my_savepoint;

5. JSON 处理

5.1 JSON 类型

• MySQL：支持 JSON 类型，但功能较基础。
• PostgreSQL：支持 JSON 和 JSONB，后者更高效。

示例：

-- MySQL
CREATE TABLE example_mysql (
    payload JSON
);
INSERT INTO example_mysql (payload) VALUES ('{"name": "Alice"}');
SELECT payload->'$.name' FROM example_mysql;

-- PostgreSQL
CREATE TABLE example_pg (
    payload JSONB
);
INSERT INTO example_pg (payload) VALUES ('{"name": "Alice"}');
SELECT payload->>'name' FROM example_pg;

6. 高级功能对比

6.1 全文搜索

• MySQL：使用 FULLTEXT 索引。
• PostgreSQL：使用 tsvector 和 tsquery。

示例：

-- MySQL
CREATE TABLE example_mysql (
    content TEXT,
    FULLTEXT(content)
);
SELECT * FROM example_mysql WHERE MATCH(content) AGAINST('database');

-- PostgreSQL
CREATE TABLE example_pg (
    content TEXT
);
UPDATE example_pg SET content = to_tsvector(content);
SELECT * FROM example_pg WHERE to_tsvector(content) @@ to_tsquery('database');

6.2 窗口函数

• MySQL：从 8.0 开始支持，语法与 PostgreSQL 基本一致。
• PostgreSQL：长期支持，功能更完善。

示例：

-- MySQL 8.0+
SELECT 
    name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- PostgreSQL
SELECT 
    name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

6.3 递归 CTE

• MySQL：从 8.0 开始支持递归 CTE。
• PostgreSQL：长期支持递归 CTE。

示例：

-- MySQL 8.0+
WITH RECURSIVE cte AS (
    SELECT id, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id FROM categories c
    JOIN cte ON cte.id = c.parent_id
)
SELECT * FROM cte;

-- PostgreSQL
WITH RECURSIVE cte AS (
    SELECT id, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id FROM categories c
    JOIN cte ON cte.id = c.parent_id
)
SELECT * FROM cte;

7. 索引与性能优化

7.1 索引类型

• MySQL：主要支持 B-Tree 和全文索引。
• PostgreSQL：支持 B-Tree、Hash、GiST、GIN、SP-GiST、BRIN 等。

示例：

-- MySQL
CREATE INDEX idx_name ON users(name);

-- PostgreSQL
CREATE INDEX idx_name ON users USING GIN (to_tsvector('english', name));

8. 地理空间数据支持

8.1 MySQL

• 支持基本的几何类型（如 POINT, POLYGON），但功能有限。

示例：

-- MySQL
CREATE TABLE locations (
    id INT,
    location POINT
);
INSERT INTO locations VALUES (1, POINT(10, 20));

8.2 PostgreSQL

• 通过 PostGIS 扩展支持完整的地理空间功能。

示例：

-- PostgreSQL
CREATE EXTENSION postgis;
CREATE TABLE locations (
    id INT,
    location GEOMETRY(POINT, 4326)
);
INSERT INTO locations VALUES (1, ST_GeomFromText('POINT(10 20)', 4326));

9. 存储过程与函数

9.1 MySQL

• 使用 CREATE PROCEDURE 定义存储过程，语言为 SQL。

示例：

-- MySQL
DELIMITER //
CREATE PROCEDURE get_user(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

9.2 PostgreSQL

• 支持多种语言（如 PL/pgSQL、PL/Python），语法更灵活。

示例：

-- PostgreSQL
CREATE OR REPLACE FUNCTION get_user(user_id INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

10. 总结对比表

11. 适用场景建议

• 选择 PostgreSQL：
  • 需要复杂查询、窗口函数、递归 CTE。
  • 高并发场景下的事务控制。
  • 需要地理空间数据支持（GIS 应用）。
  • 高性能全文搜索需求。
• 选择 MySQL：
  • 简单的 Web 应用或轻量级业务。
  • 对分库分表有强依赖的场景。
  • 需要快速部署和易用性。