專案從MySQL切換PostgreSQL，踩了太多的坑！！！

👉 這是一個或許對你有用的社群

🐱 一對一交流/面試小冊/簡歷最佳化/求職解惑，歡迎加入「芋道快速開發平臺」知識星球。下面是星球提供的部分資料：

《專案實戰（影片）》：從書中學，往事上“練”
《網際網路高頻面試題》：面朝簡歷學習，春暖花開
《架構 x 系統設計》：摧枯拉朽，掌控面試高頻場景題
《精進 Java 學習指南》：系統學習，網際網路主流技術棧
《必讀 Java 原始碼專欄》：知其然，知其所以然

👉這是一個或許對你有用的開源專案

國產 Star 破 10w+ 的開源專案，前端包括管理後臺 + 微信小程式，後端支援單體和微服務架構。

功能涵蓋 RBAC 許可權、SaaS 多租戶、資料許可權、商城、支付、工作流、大屏報表、微信公眾號、CRM 等等功能：

Boot 倉庫：https://gitee.com/zhijiantianya/ruoyi-vue-pro
Cloud 倉庫：https://gitee.com/zhijiantianya/yudao-cloud
影片教程：https://doc.iocoder.cn

【國內首批】支援 JDK 21 + SpringBoot 3.2.2、JDK 8 + Spring Boot 2.7.18 雙版本

來源：juejin.cn/post/7356108146632163339

0、前言

原專案框架 SpringBoot + MybatisPlus + Mysql

基於 Spring Boot + MyBatis Plus + Vue & Element 實現的後臺管理系統 + 使用者小程式，支援 RBAC 動態許可權、多租戶、資料許可權、工作流、三方登入、支付、簡訊、商城等功能

專案地址：https://github.com/YunaiV/ruoyi-vue-pro
影片教程：https://doc.iocoder.cn/video/

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://資料庫地址/資料庫名?currentSchema=模式名&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

postgres相比mysql多了一層模式的概念，一個數據庫下可以有多個模式。這裡的模型名等價於以前的mysql的資料庫名。如果不指定預設是public。

這時切換流程基本就改造完了，無非就是程式碼修改下連線資訊。但是你以為到這就結束了？

一堆坑還在後面呢，畢竟是兩個完全不同資料庫在語法層面還有很多差別，接下來就是修改程式碼裡的sql語法踩坑

基於 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實現的後臺管理系統 + 使用者小程式，支援 RBAC 動態許可權、多租戶、資料許可權、工作流、三方登入、支付、簡訊、商城等功能

專案地址：https://github.com/YunaiV/yudao-cloud
影片教程：https://doc.iocoder.cn/video/

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語法: selectconvert(name, DECIMAL(20, 2))-- postgreSQL語法:selectCAST(nameasDECIMAL(20, 2))

2.6、force index 語法不存在

-- mysql語法select xx FROMuserforceindex(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 => SSto_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則沒有這個要求，非聚合列會隨機取值

錯誤例子

selectname, age, count(*)fromusergroupby age, score

這時 select name 是錯誤的，應為group by裡沒有這個欄位，要麼加上，要麼變成select min(name)

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是強資料型別，欄位型別和引數值型別之間必須一樣否則就會丟擲異常。

這時候解決辦法一般有兩種

手動修改程式碼裡的欄位型別和傳參型別保證或者 postgreSQL表字段型別，反正保證雙方一一對應
新增自動隱式轉換函式，達到類似mysql的效果

布林值和int型別型別轉換錯誤

1、select查詢時的轉換異常資訊

Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = booleanSELECT   xx fom xx    WHEREenable = ture

錯誤原因：enable欄位是smallint型別查詢卻傳了一個布林值型別

2、update更新時的轉換異常資訊

Cause: org.postgresql.util.PSQLException: ERROR: column "name" is of type smallint but expression is of type booleanupdate from xx set name = false  where  name = true

錯誤原因：在update/insert賦值語句的時候，欄位型別是smallint，但是傳參卻是布林值型別

解決辦法：

postgres資料庫新增boolean <-> smallint 的自動轉換邏輯

-- 建立函式1  smallint到boolean到轉換函式CREATEORREPLACEFUNCTION"smallint_to_boolean"("i" int2)RETURNS"pg_catalog"."bool"AS $BODY$BEGINRETURN (i::int2)::integer::bool;END; $BODY$LANGUAGE plpgsql VOLATILE-- 建立賦值轉換1createcast (SMALLINTasBOOLEAN) withfunction smallint_to_boolean as ASSIGNMENT;-- 建立函式2    boolean到smallint到轉換函式CREATEORREPLACEFUNCTION"boolean_to_smallint"("b"bool)RETURNS"pg_catalog"."int2"AS $BODY$BEGINRETURN (b::boolean)::bool::int;END; $BODY$LANGUAGE plpgsql VOLATILE-- 建立隱式轉換2createcast (BOOLEANasSMALLINT) withfunction boolean_to_smallint as implicit;

如果想重來可以刪除掉上面建立的函式和轉換邏輯

-- 刪除函式dropfunction smallint_to_boolean-- 刪除轉換dropCAST (SMALLINTasBOOLEAN)

主要不要亂新增隱式轉換函式，可能導致 Could not choose a best candidate operator 異常和 # operator is not unique 異常就是在運算子比較的時候有多個轉換邏輯不知道用哪個了，死迴圈了

3、PostgreSQL輔助指令碼

3.1、批次修改timestamptz指令碼

批次修改表字段型別 timestamptz 為 timestamp，因為我們說過前者無法與LocalDateTime對應上

❝

ps：

timestamp without time zone 就是 timestamp
timestamp with time zone 就是 timestamptz

❞

DO $$DECLARE    rec RECORD;BEGINFOR rec INSELECT table_name, column_name,data_typeFROM information_schema.columnswhere table_schema = '要處理的模式名'AND data_type = 'timestamp with time zone'LOOPEXECUTE'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' TYPE timestamp';ENDLOOP;END $$;

3.2、批次設定時間預設值指令碼

批次修改模式名下的所有欄位型別為timestamp的並且欄位名為 create_time 或者 update_time的欄位的預設值為 CURRENT_TIMESTAMP

-- 注意 || 號拼接的後面的字串前面要有一個空格DO $$DECLARE    rec RECORD;BEGINFOR rec INSELECT table_name, column_name,data_typeFROM information_schema.columnswhere table_schema = '要處理的模式名'AND data_type = 'timestamp without time zone'-- 修改的欄位名and column_name in ('create_time','update_time')LOOPEXECUTE'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' SET DEFAULT CURRENT_TIMESTAMP;';ENDLOOP;END $$;