MySQL 和 PostgreSQL 是两款广泛使用的关系型数据库管理系统（RDBMS），它们虽然都遵循 SQL 标准，但在具体实现、功能特性、适用场景等方面存在一些显著的异同点。以下是一些关于 MySQL 与 PostgreSQL 使用上的异同点总结：
相同点：
-
SQL兼容性： 两者均支持标准的 SQL 语言进行数据操作，包括创建表、插入、更新、删除数据，以及执行查询等基本功能。
-
数据模型： 都基于关系型数据模型，支持定义各种数据类型（如整数、浮点数、字符串、日期/时间等）、建立表之间的关系（如外键约束）以及实现事务处理。
不同点：
-
支持的数据类型不同： PostgreSQL 相比MySQL支持更多数据类型。
-
存储过程与函数： PostgreSQL 在存储过程和函数方面提供了更强的功能支持，如更丰富的数据类型、条件表达式（如 CASE WHEN）、以及本地缓存执行计划的能力。MySQL 的存储过程功能相对简化，尤其是在早期版本中。
-
索引与查询优化： PostgreSQL 支持更多的索引类型（如 BRIN、GiST、GIN 等），在处理复杂查询和大数据量时，其查询优化器往往表现出更强的性能和适应性。MySQL 以索引组织表（如 InnoDB 存储引擎）为主，适合快速查询和高并发场景，但可能在某些复杂的表连接或数据分析查询上需要更精细的索引设计和调优。
-
特定功能差异： PostgreSQL 支持全外连接（FULL OUTER JOIN），而 MySQL 也支持但可能需要通过 UNION 操作实现。在处理 NULL 值时，PostgreSQL 使用
COALESCE()函数替代 MySQL 中的IFNULL()。时间函数方面，PostgreSQL 使用date_part等函数代替 MySQL 中的DATEDIFF，还有如窗口函数、递归查询、全文检索等。
细节部分：
PostgreSQL多支持的数据类型
-
数组类型：
-
PostgreSQL 支持创建一维或多维数组，可以存储同类型元素的数组，如
integer[ ]、text[ ]等。MySQL 也有类似的概念（如JSON类型可以存储数组结构），但没有原生的数组数据类型。
-
-
复合类型：
-
PostgreSQL 允许用户自定义复合类型（Composite Types），将多个字段组合成一个单一的数据结构，类似于编程语言中的结构体。MySQL 不直接支持这种复合类型，但可以通过创建包含多个字段的表来模拟类似效果。
-
-
范围类型：
-
PostgreSQL 提供了一系列范围类型，如
int4range、daterange等，用于表示一个连续的数值或日期范围。MySQL 没有直接对应的范围类型。
-
-
枚举类型：
-
虽然两者都支持
ENUM类型，但 PostgreSQL 的枚举类型 (ENUM或通过CREATE TYPE AS ENUM) 在功能上更为丰富，比如可以添加或删除枚举值，而 MySQL 的ENUM类型一旦创建后其成员是固定的，更改需要修改表结构。
-
-
几何类型：
-
PostgreSQL 提供了一套全面的地理空间数据类型（如
point,line,polygon,box,path,circle等）和丰富的空间索引、操作函数及扩展（如 PostGIS）。MySQL 也有地理空间支持，但其类型和功能集相对较少，直到 MySQL 8.0 才引入了一些与 PostgreSQL 更接近的功能。
-
-
JSONB 类型：
-
虽然 MySQL 8.0 引入了
JSON类型，并在后续版本中增强了对 JSON 的查询支持，但 PostgreSQL 的jsonb类型提供了更高效的存储（二进制格式）和查询能力，包括原生的 GIN 索引支持、丰富的 JSON 查询函数和操作符等。
-
-
全文检索类型：
-
PostgreSQL 提供了专门的全文检索类型
tsvector和查询表达式类型tsquery，以及一套完整的全文检索框架，包括词典管理、文本解析、查询构建等功能。MySQL 的全文检索功能相对较弱，直到 MySQL 5.6 才引入了全文索引，但其功能和灵活性不及 PostgreSQL。
-
-
其他特殊类型：
-
HSTORE：PostgreSQL 有一个名为
hstore的键值对存储类型，用于存储类似 JSON 的键值对数据，但在查询和操作上比 JSON 更轻量级。 -
CIDR/IP 类型：PostgreSQL 提供了
inet和cidr类型，专门用来存储和操作 IPv4/IPv6 地址及子网掩码或前缀长度，MySQL 需要通过字符串或二进制类型间接实现类似功能。 -
UUID 类型：PostgreSQL 有原生的
uuid类型，用于存储全局唯一标识符（UUID）。MySQL 也可以存储 UUID，但通常需要使用CHAR或BINARY类型。 -
Money 类型：PostgreSQL 有
money类型，专用于存储货币值，带有适当的舍入规则和格式化输出。MySQL 通常使用数值类型搭配自定义舍入逻辑来模拟类似功能。
-
索引以及索引的使用
（1）. **索引类型**： - MySQL支持多种类型的索引，包括B-tree索引、哈希索引、全文索引等。 - PostgreSQL主要使用B-tree索引，也支持哈希索引、GiST索引、GIN索引等。
（2）. **部分索引**： - PostgreSQL支持部分索引，允许在索引中只包含表中满足特定条件的行。 - MySQL在某些情况下可以使用函数索引来实现类似的功能，但不支持直接创建部分索引。
（3）. **并发性**： - PostgreSQL在处理并发读写时通常比MySQL更强大，特别是在索引维护方面。 - MySQL在某些情况下可能会有锁竞争问题，需要谨慎处理并发性。
（4）. **索引的可见性**： - 在MySQL中，索引是存储在表空间中的，而在PostgreSQL中，索引是一个独立的对象，可以与表分开存储。
建表建立索引以及索引使用的不同：
#PGSQL
DROP TABLE IF EXISTS test_3;
CREATE TABLE test_3(
id bigint NOT NULL,
org_id VARCHAR(32),
application_id VARCHAR(32),
vc_id VARCHAR(16) NOT NULL,
PRIMARY KEY (id)
);
#定义字段名
COMMENT ON TABLE test_3 IS '测试3';
COMMENT ON COLUMN test_3.id IS 'ID';
COMMENT ON COLUMN test_3.org_id IS '组织id';
COMMENT ON COLUMN test_3.application_id IS '应用id';
COMMENT ON COLUMN test_3.vc_id IS '租户号';
#建立联合索引
CREATE INDEX idx_test ON test_3(org_id,application_id,vc_id);
#MYSQL
DROP TABLE IF EXISTS test_3;
CREATE TABLE test_3(
`id` BIGINT NOT NULL COMMENT 'ID' ,
`org_id` VARCHAR(32) COMMENT '组织id' ,
`application_id` VARCHAR(32) COMMENT '应用id' ,
`vc_id` VARCHAR(16) NOT NULL COMMENT '租户号' ,
PRIMARY KEY (id),
KEY `idx_test`(`org_id`,`application_id`,`vc_id`)
) ENGINE=InnoDB COMMENT = '测试3';
#建立联合索引
CREATE INDEX idx_test ON test_3(org_id,application_id,vc_id);注意事项：查询使用联合索引时MYSQL必须使用最左前缀原则，即查询条件必须按联合索引顺序，才能真正用到索引，但是PGSQL可以对联合索引的条件任意搭配，都可以使用索引
官方文档
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but do not use an index to perform lookups because (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).
A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they'd still have to be scanned through. This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.
常用函数
1.日期函数
| 日期函数功能 | PostgreSQL | MySQL |
| 日期加减 | timestamp '2001-09-28 01:00' + interval '23 hours' |
|
| 日期差值 |
|
|
| 日期格式化 |
|
|
| 日期解析 |
|
|
| 日期范围条件 |
|
|
| 日期组件提取 |
|
|
| 时间戳转换 |
|
|
| 时间戳生成 |
|
|
| 工作日计算 |
|
|
| 特定日期生成 |
|
通过加减日期函数组合实现 |
2.字符串函数
| 字符串函数功能 | PostgreSQL | MySQL |
| 字符串拼接 |
|
|
| 连接多值分隔符 |
|
|
| 子串截取 |
|
|
| 去除首尾空格 |
|
|
| 查找子串位置 |
|
|
| 分割字符串 |
|
|
| 字符串长度 |
|
|
| 字符串比较 |
|
|
| 正则匹配与替换 |
|
无内置正则替换函数 (需使用用户定义函数或 |
| 格式化数字为字符串 |
(适用于数字转字符串) |
(适用于数字、日期等多种类型转字符串) |
3.类型转换函数
| 操作场景 | PostgreSQL函数 | MySQL函数 |
| 整数转字符串 |
|
|
| 字符串转整数 |
|
|
| 浮点数转字符串（格式化） |
|
|
| 字符串转浮点数 |
|
|
| 日期转字符串 |
|
|
| 字符串转日期 |
|
|
| 时间戳转字符串 |
|
|
| 字符串转时间戳 |
|
|
| 布尔值转字符串 |
|
|
| 字符串转布尔值 |
|
|
特定功能
1.DDL操作
PGSQL
加字段、可变长字段类型长度改大不会锁表，所有的DDL操作都不需要借助第三方工具，并且跟商业数据库一样，DDL操作可以回滚，保证事务一致性
MySQL
由于大部分DDL操作都会锁表，例如加字段、可变长字段类型长度改大，所以需要借助percona-toolkit里面的pt-online-schema-change工具去完成操作
将影响减少到最低，特别是对大表进行DDL操作
DDL操作不能回滚
2.WITH查询
WITH查询是PostgreSQL支持的高级SQL特性之一，这一特性常称为CTE(Common Table Expressions)，WITH查询在复杂查询中定义一个辅助语句（可理解成在一个查询中定义的临时表），这一特性常用于复杂查询或递归查询应用场景
3.递归查询
- sql中WITH xxxx AS () 是对一个查询子句做别名，同时数据库会对该子句生成临时表；
- WITH RECURSIVE 则是一个递归的查询子句，他会把查询出来的结果再次代入到查询子句中继续查询
语法：
with recursive 名字 as (
A.初始语句（非递归部分）
union all
B.递归部分语句
) [SELECT | INSERT | UPDATE | DELETE]例如，存在一张包含如下数据的表。
id name fatherid
1 中国 0
2 辽宁 1
3 山东 1
4 沈阳 2
5 大连 2
6 济南 3
7 和平区 4
8 沈河区 4使用PostgreSQL的WITH查询检索ID为7以及以上的所有父节点，如下：
WITH RECURSIVE r AS (
SELECT * FROM test_area WHERE id = 7
UNION ALL
SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid
)
SELECT * FROM r ORDER BY id;查询结果如下：
id | name | fatherid
----+--------+----------
1 | 中国 | 0
2 | 辽宁 | 1
4 | 沈阳 | 2
7 | 和平区 | 4
(4 rows)4.string_agg函数
首先介绍string_agg函数，此函数语法如下：
string_agg(expression, delimiter)简单的说string_agg函数能将结果集某个字段的所有行连接成字符串，并用指定delimiter分隔符分隔，expression表示要处理的字符类型数据；参数的类型为(text, text) 或 (bytea, bytea)，函数返回的类型同输入参数类型一致，bytea属于二进制类型，使用情况不多，我们主要介绍text类型输入参数，本节开头的场景正好可以用string_agg函数处理。
将city字段连接成字符串如下：
mydb=> SELECT string_agg(city,',') FROM city;
string_agg
--------------------------
台北,香港,上海,东京,大阪
(1 row)5.array_agg函数
array_agg函数和string_agg函数类似，最主要的区别为返回的类型为数组，数组数据类型同输入参数数据类型一致，array_agg函数支持两种语法，第一种如下： array_agg(expression) --输入参数为任何非数组类型
输入参数可以是任何非数组类型，返回的结果是一维数组，array_agg函数将结果集某个字段的所有行连接成数组，执行以下查询。
mydb=> SELECT country,array_agg(city) FROM city GROUP BY country;
country | array_agg
---------+------------------
日本 | {东京,大阪}
中国 | {台北,香港,上海}array_agg函数输出的结果为字符类型数组，其他无明显区别，使用array_agg函数主要优点在于可以使用数组相关函数和操作符。
基础使用教程见：PostgreSQL菜鸟教程 mysql菜鸟教程
660
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
