从 MySQL 切换 PostgreSQL

最新推荐文章于 2024-08-25 08:18:26 发布

十离

最新推荐文章于 2024-08-25 08:18:26 发布

阅读量964

点赞数 15

文章标签： mysql postgresql 数据库

本文链接：https://blog.csdn.net/qq_43741851/article/details/140227975

版权

从 MySQL 切换 PostgreSQL

前言
原项目框架 SpringBoot + MybatisPlus + Mysql

1、切换流程

1.1、项目引入postgresql驱动包
由于我们要连接新的数据库，理所当然的要引入该数据库的驱动包，这与mysql驱动包类似

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

1.2、修改jdbc连接信息
之前用的是mysql协议，现在改成postgresql连接协议

spring:
  datasource:
    # 修改驱动类
    driver-class-name: org.postgresql.Driver
    # 修改连接地址
    url: jdbc:postgresql://数据库地址/数据库名

mysql的数据库名。如果不指定默认是public。

这时切换流程基本就改造完了，无非就是代码修改下连接信息。但是并没结束奥利给！！

一堆坑还在后面呢，毕竟是两个完全不同数据库在语法层面还有很多差别，接下来就是修改代码里的sql语法踩坑

2、踩坑记录

2.1、TIMESTAMPTZ类型与LocalDateTime不匹配

异常信息:

PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type java.time.LocalDateTime.

如果postgres表的字段类型是TIMESTAMPTZ ，但是java对象的字段类型是LocalDateTime，这时会无法转换映射上。postgres表字段类型应该用timestamp 或者 java字段类型用Date

2.2、参数值不能用双引号

错误例子:

 WHERE name = "jay"   ===>    WHERE name = 'jay'

这里参数值"jay" 应该改成单引号 ‘jay’

2.3、字段不能用``包起来

错误例子

 WHERE `name` = 'jay'  ==>    WHERE name = 'jay'

这里的字段名name不能用``选取

2.4、json字段处理语法不同

-- mysql语法: 
WHERE keywords_json->'$.name' LIKE CONCAT('%', ?, '%')

-- postgreSQL语法:
WHERE keywords_json ->>'name' LIKE CONCAT('%', ?, '%')

获取json字段子属性的值mysql是用 -> '$.xxx’的语法去选取的，而 postgreSQL 得用 ->>‘xx’ 语法选择属性

2.5、convert函数不存在

postgreSQL没有convert函数，用CAST函数替换

-- mysql语法: 
select convert(name, DECIMAL(20, 2))

-- postgreSQL语法:
select CAST(name as DECIMAL(20, 2))

2.6、force index 语法不存在

-- mysql语法
select xx FROM user force index(idx_audit_time)

mysql可以使用force index强制走索引， postgres没有，建议去掉

2.7、ifnull 函数不存在

postgreSQL没有ifnull函数，用COALESCE函数替换

异常信息

cause: org.postgresql.util.PSQLException: ERROR: function ifnull(numeric, numeric) does not exist

2.8、date_format 函数不存在
异常信息

Cause: org.postgresql.util.PSQLException: ERROR: function date_format(timestamp without time zone, unknown) does not exist

postgreSQL没有date_format函数，用to_char函数替换

替换例子:

// %Y => YYYY 
// %m  =>   MM
// %d   =>  DD
// %H => HH24
// %i => MI
// %s => SS
to_char(time,'YYYY-MM-DD') => DATE_FORMAT(time,'%Y-%m-%d')
to_char(time,'YYYY-MM') => DATE_FORMAT(time,'%Y-%m')
to_char(time,'YYYYMMDDHH24MISS') => DATE_FORMAT(time,'%Y%m%d%H%i%s')

2.9、group by语法问题
异常信息

Cause: org.postgresql.util.PSQLException: ERROR: column  "r.name" must appear in the GROUP BY clause or be used in an  aggregate function

postgreSQL 的 selectd的字段必须是group by的字段里的或者使用了聚合函数。mysql则没有这个要求，非聚合列会随机取值
2.10、事务异常问题

异常信息

# Cause: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

; uncategorized SQLException; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

Postgres数据库中，同一事务中如果某次数据库操作中出错的话，那这个事务以后的数据库操作都会出错。正常来说不会有这种情况，但是如果有人去捕获了事务异常后又去执行数据库操作就会导致这个问题。则mysql貌似不会。
2.11 类型转换异常
为mysql是支持自动类型转换的。在表字段类型和参数值之间如果类型不一样也会自动进行转换。而postgreSQL是强数据类型，字段类型和参数值类型之间必须一样否则就会抛出异常。

一般有两种

（1）手动修改代码里的字段类型和传参类型保证或者 postgreSQL表字段类型，反正保证双方一一对应
（2）添加自动隐式转换函数，达到类似mysql的效果
布尔值和int类型类型转换错误

4、注意事项
1、将数据表从mysql迁移postgres 要注意字段类型要对应不要变更（*）

2、原先是 tinyint的就变samllint类型，不要是bool类型，有时代码字段类型可能对应不上

3、如果java字段是LocalDateTime原先mysql时间类型到postgres后不要用TIMESTAMPTZ类型

4、mysql一般用tinyint类型和java的Boolean字段对应并且在查询和更新时支持自动转换，但是postgres是强类型不支持，如果想无缝迁移postgres内部就新增自动转换的隐式函数，但是缺点是每次部署postgres后都要去执行一次脚本。