postgres和mysql在语法的区别（ PostgreSQL 16 vs MySQL 8.0+，兼容 2025 年现状）
postgres和mysql在语法的区别（ PostgreSQL 16 vs MySQL 8.0+，兼容 2025 年现状）
以下是 PostgreSQL 与 MySQL 在语法上的主要区别汇总（截至 PostgreSQL 16 / MySQL 8.0+，兼容 2025 年现状）：
| 类别 | 特性 | PostgreSQL(v16) | MySQL(8+) |
|---|---|---|---|
| 1. 数据类型 | 布尔类型 | BOOLEAN 或 BOOL（原生支持，值：TRUE/FALSE/NULL） | 无原生 BOOLEAN；BOOL/BOOLEAN 是 TINYINT(1) 的别名（0/1） |
| 字符串类型 | TEXT（无长度限制，性能与 VARCHAR(n) 相当）；VARCHAR(n)；CHAR(n) | TEXT（分 TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT）；VARCHAR(n) 需指定长度；CHAR(n) | |
| 自增主键 | SERIAL（旧）或 GENERATED BY DEFAULT AS IDENTITY（SQL 标准） | AUTO_INCREMENT（仅用于整型列，需配合 PRIMARY KEY 或 UNIQUE） | |
| JSON 类型 | JSON（存储原始文本）和 JSONB（二进制格式，支持索引和高效查询） | JSON（内部以二进制存储，类似 JSONB；无 JSONB 类型） | |
| 数组类型 | 原生支持（如 INT[], TEXT[]），可索引、查询 | 不支持原生数组；需用 JSON 或冗余设计模拟 | |
| 枚举类型 | ENUM（需先 CREATE TYPE 定义） | ENUM('val1','val2',...)（列定义时直接声明） | |
| 日期/时间 | TIMESTAMP 默认不带时区；TIMESTAMPTZ 带时区（推荐） | TIMESTAMP 默认 带时区转换（存储为 UTC，检索转为 session 时区）；DATETIME 无时区 | |
| 2. DDL（建表与修改） | 创建自增列 | id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 或 SERIAL | id INT AUTO_INCREMENT PRIMARY KEY |
| 修改列默认值 | ALTER COLUMN col SET DEFAULT value / DROP DEFAULT | MODIFY COLUMN col type DEFAULT value 或 ALTER COLUMN col SET DEFAULT value（8.0.13+ 支持 ALTER ... SET DEFAULT） | |
| 删除列默认值 | ALTER COLUMN col DROP DEFAULT | ALTER COLUMN col DROP DEFAULT（8.0.13+）或 MODIFY COLUMN col type | |
| 修改列类型 | ALTER COLUMN col TYPE new_type [USING expr]（可加 USING 转换） | MODIFY COLUMN col new_type（隐式转换，失败则报错） | |
| 重命名列 | ALTER TABLE t RENAME COLUMN old TO new | ALTER TABLE t RENAME COLUMN old TO new（8.0.4+）；旧版用 CHANGE COLUMN | |
| 添加列并置位置 | 不支持指定列位置（列顺序逻辑无关） | ADD COLUMN col ... AFTER another_col / FIRST | |
| 3. DML（查询与操作） | 字符串拼接 | ` | |
| 字符串转义 | 'It''s ok'（标准单引号转义） | 'It''s ok' 或 'It\'s ok'（取决于 NO_BACKSLASH_ESCAPES 模式） | |
| 正则表达式匹配 | ~（区分大小写）、~*（不区分）、!~/!~* | REGEXP / RLIKE（如 col REGEXP 'pattern'），默认不区分大小写（取决于 collation） | |
| LIMIT/OFFSET | LIMIT n OFFSET m 或 LIMIT m, n（不支持 m,n 语法） | 支持 LIMIT n OFFSET m 和 LIMIT m, n（m=offset, n=count） | |
| 返回插入 ID | INSERT ... RETURNING id（标准，可返回任意列） | LAST_INSERT_ID() 函数；或 JDBC/应用层获取；INSERT ... RETURNING（8.0.21+ 实验性支持） | |
| 4. 函数与表达式 | 字符串函数 | LENGTH()（字符数）、CHAR_LENGTH() 同义；OCTET_LENGTH()（字节数） | CHAR_LENGTH() / LENGTH()（字节数！易混淆）；CHARACTER_LENGTH() 同义 |
| 当前时间 | NOW(), CURRENT_TIMESTAMP, CLOCK_TIMESTAMP()（事务/语句级） | NOW(), CURRENT_TIMESTAMP, SYSDATE()（NOW() 是事务开始时间） | |
| 条件表达式 | CASE WHEN ... THEN ... END（标准）；支持 NULLIF(), COALESCE() | 同左；另支持 IF(condition, t, f)（非标准） | |
| 字符串连接聚合 | STRING_AGG(col, ',') | GROUP_CONCAT(col SEPARATOR ',') | |
| 窗口函数 | 全面支持（OVER(), PARTITION BY, ROWS/RANGE 等） | 8.0+ 全面支持（基本与 PG 一致） | |
| 5. 事务与锁 | 默认事务隔离级别 | READ COMMITTED | REPEATABLE READ |
| 可重复读行为 | 基于 MVCC，可能发生幻读（非序列化） | 基于间隙锁（Gap Lock），实际提供 近似 Serializable 的幻读防护（但非标准） | |
| 保存点 | SAVEPOINT s1; ROLLBACK TO s1; | 同左 | |
| 显式锁 | SELECT ... FOR UPDATE, FOR SHARE, FOR NO KEY UPDATE 等 | SELECT ... FOR UPDATE, FOR SHARE（即 LOCK IN SHARE MODE 废弃 → FOR SHARE） | |
| 6. 其他特性 | 模式（Schema） | CREATE SCHEMA s;，默认 public；多 schema 是一等公民 | SCHEMA 是 DATABASE 的同义词；实际无真正 schema 隔离（每个 db 独立） |
| 序列管理 | CREATE SEQUENCE s; nextval('s'), currval('s') | 无独立序列对象（8.0.16+ 引入 CREATE SEQUENCE，但集成度低） | |
| CTE（公用表表达式） | 支持递归与非递归；默认是 物化（可加 MATERIALIZED/NOT MATERIALIZED） | 8.0+ 支持；默认 非物化（内联展开），可通过 WITH ... AS MATERIALIZED 强制（8.0.21+） | |
| JSON 查询 | ->（返回 JSON）、->>（返回 text）；#>、#>>（路径）；@>（包含） | ->（返回 JSON）、->>（返回 text）；JSON_EXTRACT()；-> 和 ->> 8.0+ 支持 | |
| 全文检索 | tsvector/tsquery 类型；to_tsvector(), @@ 操作符；支持多语言 | FULLTEXT 索引（仅 MyISAM / InnoDB）；MATCH() AGAINST()；中文支持弱 | |
| 扩展性 | 支持自定义类型、函数（C/PL/pgSQL/Python 等）、操作符、索引方法（GiST/SP-GiST/BRIN） | 插件式存储引擎；自定义函数限 SQL/UDF（C）；JSON/地理扩展较弱 |
⚠️ 常见陷阱举例：
| 场景 | PostgreSQL 行为 | MySQL 行为 |
|---|---|---|
INSERT INTO t (id) VALUES (NULL)（自增列） | SERIAL/IDENTITY 列插入 NULL → 自动生成新值 | AUTO_INCREMENT 列插入 NULL → 自动生成新值 ✅；但若显式插入 0 且 NO_AUTO_VALUE_ON_ZERO 未开启 → 也生成新值（易混淆） |
GROUP BY 非聚合列 | 严格：仅允许 GROUP BY 列或聚合函数（除非 GROUP BY 主键） | 默认宽松（sql_mode 不含 ONLY_FULL_GROUP_BY 时）→ 返回“任意”值，可能误导 |
'' vs NULL | '' 是空字符串 ≠ NULL | 同左，但某些旧版本/配置下 INSERT INTO t (char_col) VALUES ('') 可能转为 NULL（若 NOT NULL 且 sql_mode 含 STRICT 则报错） |
COUNT(NULL) | 0（因 NULL 不计入） | 0 |
✅ 相关建议：
- 迁移时注意：
LIMIT写法、字符串拼接、日期时区、自增机制、GROUP BY严格性。 - 开发中优先使用 标准 SQL（如
STRING_AGGvsGROUP_CONCAT可封装适配层）。 - MySQL 用户转 PG：习惯
RETURNING、JSONB、数组、Schema 隔离。 - PG 用户转 MySQL：警惕
AUTO_INCREMENT间隙、DATETIME无时区、LENGTH()字节陷阱。
附PostgreSQL 最佳实践
PostgreSQL 16 关键配置参数及最佳实践
一、核心配置文件
PostgreSQL 16的主要配置文件是postgresql.conf，用于调整各种性能参数，如内存使用、连接数限制等。 另外，pg_hba.conf文件用于设置访问控制策略，是安全配置的重要组成部分。
二、关键内存配置参数
1. shared_buffers
- 作用：设置数据库服务器使用的共享内存缓冲区数量。
- 推荐值：通常设置为系统总内存的25%。例如，对于16GB内存的系统，建议设置为4GB。
- 注意事项：修改后必须重启服务器才能生效。
2. work_mem
- 作用：设置查询操作（如排序或哈希表）在写入临时磁盘文件之前可以使用的最大内存量。
- 推荐值：通常在10-50MB之间，具体取决于并发连接数和工作负载。
- 计算公式：work_mem × max_connections 不应超过系统总内存的75%。
3. maintenance_work_mem
- 作用：控制维护操作（如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）使用的最大内存量。
- 推荐值：
- 一般系统：设置为1GB
- 32GB以上内存：建议1GB
- 64GB以上内存：建议2GB
- 128GB以上内存：建议4GB
4. effective_cache_size
- 作用：影响查询计划器的决策，表示操作系统和PostgreSQL可以使用的总缓存量。
- 推荐值：通常设置为系统总内存的75%。如果设置过低，查询计划器可能会忽略某些索引。
三、连接配置参数
max_connections
- 作用：设置数据库服务器允许的最大并发连接数。
- 推荐值：根据具体项目需求设置，避免设置过高导致内存不足。
- 最佳实践：对于高并发场景，建议使用连接池（如pgBouncer）而不是直接增加max_connections。
四、WAL（预写日志）相关参数
PostgreSQL 16通过新的查询规划器优化提升了性能，包括并行执行FULL和RIGHT连接的能力。 WAL配置对数据安全和性能至关重要，需要根据I/O性能和数据安全需求进行调整。
五、最佳实践
1. 配置管理
- 使用专用配置文件：可以将共享配置放在
shared.conf中，内存相关配置放在memory.conf中，便于不同规格服务器的统一管理。 - 修改配置后，某些参数需要重启生效，而有些可以通过
pg_reload_conf()重新加载。
2. 性能优化
- 硬件匹配：调整PostgreSQL的配置参数以匹配您的硬件资源和工作负载。
- 监控调整：定期监控数据库性能，根据实际负载调整参数。
- 索引优化：除了配置参数，合理的索引设计也是性能优化的关键因素。
3. 安全配置
- 配置适当的密码策略，避免使用安全系数低的密码。
- 限制网络访问范围，建议CIDR前缀不小于/16，最好大于/19。
- 启用认证延迟（auth_delay.milliseconds）来增加暴力破解攻击的难度。
4. 备份策略
- 实施每日全量备份策略。
- 定期检查日志文件中的错误信息，如使用命令：
grep -i error postgresql-16-main.log。
六、配置调整方法
修改PostgreSQL配置有两种主要方式：
- 直接编辑
postgresql.conf文件。 - 使用SQL命令：
ALTER SYSTEM SET parameter_name = 'value';
对于专用数据库服务器，可以将effective_cache_size设置为系统总内存的75%，并根据特定的服务器工作负载进行调整。 通过PGTune等工具可以为配置参数提供一个很好的起点。
注意事项：性能优化的关键是根据实际工作负载进行持续监控和调整，没有放之四海而皆准的配置方案。
postgres和mysql在语法的区别（ PostgreSQL 16 vs MySQL 8.0+，兼容 2025 年现状）
