盘点一下PostgreSQL和MySQL的区别点

上期有说，数据环境切换，由MySQL换成PostgreSQL。
那么为了大家以后少踩坑，简单总结了下我曾踩过的坑和需要注意的点。
首先PostgreSQL有一个模式的概念。
一、格式区别：

和Oracle一样,PostgreSQL也是严格区分大小写。

二、符号区别：

和Oracle一样PostgreSQL中，" " 双引号是区分库名，关键字等，而MySQL则是

反单引号

(tab键上方的键)，pg查询时字符类型的字段必须使用单引号，而MySQL带有优化器（不是必须）。

三、自增区别：

MySQL中使用auto_increment ，在需要的列指定自增，而pg中需要设置自增序列。

（1）使用SQL语句
①创建表时

CREATEtable infisa_template_config(id serial);

②表已存在

<--设置序列从1开始，自增1-->CREATE SEQUENCE user_id_seq STARTWITH1  
INCREMENT BY1NO MINVALUE  NO MAXVALUE  CACHE 1;<--设置序列-->ALTERtableuserALTERcolumn id SETDEFAULT nextval('user_id_seq');

(2)使用可视化工具
连接navicat15（或者使用DBeaver）

点击序列

可以通过可视化界面新建序列

注：同步数据的话，推荐通过sql方式新建序列。

四、函数区别：
(1)时间转化

①时间转字符串：

MySQL: date_format(a.tag_create_date,‘%Y-%m-%d %H:%i:%s’)

PostgreSQL: to_char(a.tag_create_date,‘yyyy-mm-dd HH:MM:SS’)

②字符串转时间：

MySQL: date_format(a.tag_create_date,‘%Y-%m-%d %H:%i:%s’)

PostgreSQL: to_date(a.tag_create_date,‘yyyy-mm-dd HH:MM:SS’)

(2) IFNULL()函数

MySQL: IFNULL(a.idm,‘’)

PostgreSQL: COALESCE(a.id,‘’)

(3) sysdate()函数

MySQL: SELECT sysdate()

PostgreSQL: SELECT now()

(4) find_in_set()函数（允许在逗号分隔的字符串列表中查找指定字符串的位置）

MySQL: SELECT t.dept_id FROM sys_dept t WHERE find_in_set(‘100’, ancestors)

PostgreSQL: SELECT t.dept_id FROM sys_dept t WHERE ‘100’ = ANY (string_to_array(ancestors, ‘,’))

(5)group_concat()函数

MySQL: select a.name,group_concat(distinct city)from user_city a group by a.name;

PostgreSQL: select a.name,array_to_string(array_agg(distinct a.city),‘,’)from user_city a group by a.name;

(6)LIMIT

MySQL: select id,name from hospital.ods_user_basic limit 10,2;

PostgreSQL: select id,name from hospital.ods_user_basic limit 10 offset 2;

（7）DISTINCT

MYSQL: select DISTINCT b.id from hospital.ods_user_basic as b

PostgreSQL: select DISTINCT ON (b.id) b.* from hospital.ods_user_basic as b

(注：pg中祛重+排序 ，使用distinct on (列1) order by 列1 )

（8）MyBatis-Plus整合MySQL、PostgreSQL，LIKE使用
MySQL：

<selectid="checkReportPage"resultMap="reportResultMap">
       SELECT
       *
       FROM
       infisa_medical_report
       WHERE is_deleted=0 AND status = 1 AND name LIKE concat('%',#{handleTask.name},'%')
    </select>

PostgreSQL:

<selectid="checkReportPage"resultMap="reportResultMap">
       SELECT
       *
       FROM
       infisa_medical_report
       WHERE is_deleted=0 AND status = 1 AND name like concat('%',#{handleTask.name}::varchar,'%')
    </select>

（注：pg必须指定模糊查询列的数据类型，否则会报错）

五、数据类型转化（针对于PostgreSQL）：
MySQL、Oracle等都是默认对数据类型进行了隐式的转换，在其他数据库varchar等字符串类型和数字可以进行自动的隐式转换，但是PG确没有这么处理，官方文档中也有针对pg的数据转换方式。

解决办法：
①强转（在查询字段上指定数据类型）

a.a1 = b.b1::int8 或者 a.a1::varchar = b.b1

②隐式类型转化（创建类型转换）

–注:创建cast需要有pg_cast系统表的权限
–注:当创建类型转换使用自动隐式转换的话如果出现多个匹配的转换此时pg会因为不知道选择哪一个去处理类型转换而报错，
–如果出现多个隐式自动转换都匹配此时还是需要手动添加转换以达到效果，或者删除多余的类型转换
CREATE CAST (INTEGER AS VARCHAR) WITH INOUT AS IMPLICIT;
CREATE CAST (VARCHAR AS INTEGER) WITH INOUT AS IMPLICIT;
CREATE CAST (BIGINT AS VARCHAR) WITH INOUT AS IMPLICIT;
CREATE CAST (VARCHAR AS BIGINT) WITH INOUT AS IMPLICIT;

附：
③查询当前类型转化：

–这个查询是当前所有的CAST，具体字段的定义同样可以参阅PG数据库官方文档里的描述
select
(select typname from pg_type where oid = t.castsource) as “castsource”,
(select typname from pg_type where oid = t.casttarget) as “casttarget”,
castcontext,
castmethod
from pg_cast as t

④删除类型转化：

DROP CAST (varchar as bigint); DROP CAST (bigint as varchar);