mysql 和 postger常见不同

最全持续更新...

目录

pg强于mysql的地方

稳定性

性能

数据类型

“无锁定”特性

函数和条件索引

SQL 编程能力

TEXT 类型

复制的特性

标准实现

存储过程

表连接支持

数据量

PG 不如 MySQL 的地方

其他

其他语法不同

大小写敏感问题

boolean类型

offset/limit

upsert/replace:

索引

约束

事务隔离级别

条件判断

保留小数位(round)

mysql ifnull  = pg COALESCE

mysql  FIND_IN_SET (#{deptId},ancestors)= pg #{deptId} ::varchar = ANY(STRING_TO_ARRAY(ancestors, ','))

添加自增长

获取当前时间

---

pg强于mysql的地方

稳定性

• PostgreSQL 的稳定性极强，Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步
• 很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的，相比之下，PG数据库这方面要好一些

性能

• PG：在高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降
• MySQL： 明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）。

数据类型

• PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型
• mysql就差很多
• PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial

“无锁定”特性

• PG 的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系

函数和条件索引

• PG 的可以使用函数和条件索引，这使得PG数据库的调优非常灵活
• mysql就没有这个功能，条件索引在web应用中很重要

SQL 编程能力

• PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（ORACLE的叫法，PG里叫window函数），还可以用多种语言来写存储过程，对于R的支持也很好。
• MYSQL就差的很远，很多分析功能都不支持，腾讯内部数据存储主要是MYSQL，但是数据分析主要是HADOOP+PGSQL。

TEXT 类型

• 一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问
• PG 的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库都可以省了。

复制的特性

• 对于WEB应用来说，复制的特性很重要
• mysql到现在也是异步复制
• pgsql可以做到同步，异步，半同步复制
• mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制
• pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。
• PG的主备复制属于物理复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小

标准实现

• 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；

存储过程

• 存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力

表连接支持

• PG对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强

数据量

• MySQL采用索引组织表
• PG主表采用堆表存放能够支持比MySQL更大的数据量。

PG 不如 MySQL 的地方

• MySQL有一些实用的运维支持，如 slow-query.log ，这个pg肯定可以定制出来，但是如果可以配置使用就更好了。
• mysql的innodb引擎，可以充分优化利用系统所有内存，超大内存下PG对内存使用的不那么充分
• MySQL的复制可以用多级从库，但是在9.2之前，PGSQL不能用从库带从库。
• 测试结果上看，mysql 5.5的性能提升很大，单机性能强于pgsql，5.6应该会强更多.
• web应用来说,mysql 5.6 的内置MC API功能很好用，PGSQL差一些
• MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。
• MySQL的优化器较简单，系统表、运算符、数据类型的实现都很