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的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作；

其他

存储过程，函数，视图之类的功能，现在两个数据库都可以支持了
说mysql的执行速度比pgsql快很多是不对的，速度接近，而且很多时候取决于你的配置。
多线程架构和多进程架构之间没有绝对的好坏，oracle在unix上是多进程架构，在windows上是多线程架构
对于事务的支持，mysql和pgsql都没有问题

其他语法不同

大小写敏感问题

【.】对于数据的大小写敏感问题

mysql字符串匹配默认不区分大小写
pgsql中默认区分大小写

【.】对于名称的大小写敏感问题

PG 对字段大小写敏感并默认对SQL语句转化为小写，不论是写select Name还是NAME还是NAmE，统统转化为name，所以，如果你的数据库字段名为Name 那么会提示的是没有name
PG 如果你的字段为 Name，那么操作的时候。只需要加一个双引号就可以了，select "Name"
所以 pg 需要注意
表字段或表名为小写时，字段或表名不需要双引号
表字段或表名为大写时，字段或表名必须添加双引号

mysql

数据库名与表名是严格区分大小写的
表的别名是严格区分大小写的
列名与列的别名在所有的情况下均是忽略大小写的
编译的时候，操作系统会将所有字符转换成大写的，再进行编译。所以建议在写sql语句时。所有的都用大写，编译的时候可以节省转化的时间如 SELECT * FROM TB_USER

boolean类型

mysql并不支持boolean类型，当我们创建了boolean的字段属性之后，mysql会自动将其转化为tinyint(1)类型。当插入“true”的时候，其值自动转化为1。当插入"false"的时候，其值自动转化为0，使用select进行取值的时候，我们搜索出来的也是0和1
而pgsql是支持boolean（bool）的，所以我们可以插入“true”和“false”的类型。

offset/limit

mysql和pg中都支持offset/limit的分页语法，但是两者有一点不同

mysql> select * from t1 limit 2,2;
pg>select * from tbl limit 2 offset 2;

upsert/replace:

pg中的upsert作用是当插入数据时：如果不存在则insert，存在则update。
mysql中使用replace来实现类似的功能。

索引

mysql

btree索引
invert索引，即倒排索引，常用来实现多值类型、json类型、全文检索等的索引查询
表达式索引，mysql中的表达式索引不支持spatial和fulltext类型。
空间索引，mysql中不支持空间索引，其实现空间索引的方式是将空间对象转换成geohash编码，然后使用btree索引来实现。.

支持多种索引类型：btree、hash、gin、gist、sp-gist、bloom、rum、brin；
支持exclude索引、表达式索引、partial索引(分区索引)；
支持空间索引，是真正的基于rtree的空间索引类型；
pg开发了多种索引接口，用户可以自定义新的索引

约束

mysql和pg一样都支持主键约束、外键约束、唯一约束、not null约束等

两者在约束方面的区别

mysql

check约束不是强制的，即可以创建check约束，但是违反该约束的数据仍然不会报错；exclude排它约束mysql中不支持。

check约束是强制的，如果数据不符合check约束则无法插入。并且pg中还支持exclude约束。

事务隔离级别

pg和mysql虽然都支持四种事务隔离级别

pg中read uncommitted的隔离级别是不可用的，这也确保了在pg中不会出现脏读的现象。
mysql中是存在隐式提交的，即在事务中的DDL语句会被自动提交，而在pg中不会

例如：

mysql可以发现事务回滚后t2表仍然存在，因为已经自动提交了

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(222);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+------+
| id |
+------+
| 222 |
+------+
1 row in set (0.00 sec)

pg中却没有，可以被rollback

bill=# create table tt2(id int);
CREATE TABLE
bill=# insert into tt2 values(222);
INSERT 0 1
bill=# rollback ;
ROLLBACK
bill=# select * from t2;
ERROR: relation "t2" does not exist

条件判断

mysql：if(), case when 条件1 then 符合值 else 不符合值 end；
postgresql: case when 条件1 then 符合值 else 不符合值 end；

保留小数位(round)

mysql: round(字段，小数位数)
postgresql: round(case(‘字段’ as numeric),小数位数)

mysql ifnull = pg COALESCE

简介

判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值；

用于查询返回结果

ifnull(m.perms,'')
COALESCE(m.perms,'')

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

简介

字段ancestors中是否存在值，相当于 like %102%，但是数据中如果存在 01020,那就出问题了

看一下pg示例，将ancestors字段按照，分割成数组。判断是否存在102

select * from sys_dept where '102' = ANY (STRING_TO_ARRAY(ancestors,','))

看一下mysql SELECT * FROM sys_dept WHERE FIND_IN_SET (102,ancestors)

添加自增长

mysql 勾选自增长就可以了

pgsql 选择serial 之后默认会自动出来保存

再打开就是这个样子

PG 使用 sql语句设置方式


-- 首先 创建 SEQUENCE 序列 名字一般是public.sys_user_user_id_sqe 表名 + 字段名 + sqe
CREATE SEQUENCE public.sys_user_user_id_sqe
    AS integer
    START WITH 1 -- 开始为1
    INCREMENT BY 1 -- 增量为 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
		
-- 然后 将序列 归属给 表public.sys_user 的user_id 字段
ALTER  SEQUENCE public.sys_user_user_id_sqe OWNED BY public.sys_user.user_id;
-- 最后 设置
ALTER TABLE ONLY public.sys_user ALTER COLUMN user_id SET DEFAULT nextval('public.sys_user_user_id_sqe'::regclass);
----------------------------------------------------------------------------------------
-- 这个异常的解决方式duplicate key value violates unique constraint "sys_role_pkey"
-- 下面是如果你之前表有数据了。然后现在你在进行自增长，插入可能会报错。就是要自增的那个数已经存在了 
-- 首先获取最大的数值进行加1
-- SELECT setval('public.sys_user_user_id_sqe', (SELECT MAX(user_id) FROM "sys_user")+1);
-- 将这个给自增长序列
-- SELECT nextval('public.sys_user_user_id_sqe'::regclass)

获取当前时间

mysql sysdate()函数，pg now()函数

表继承

pgsql：CREATE TABLE tableNameS () INHERITS tableNameP

mysql: 无

先整理现在碰到的。项目改数据库中，碰到一些问题总结。之后会把其他常见问题总结。更新中...

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/459336