前言
原計畫框架 SpringBoot + MybatisPlus + MySQL
一、切換流程
1.計畫引入 PostgreS ql驅動包
由於我們要連線新的資料庫，理所當然要引入該資料庫的驅動包，這與MySQL驅動包類似。
<dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId></dependency>
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語法踩坑。
二、踩坑記錄
1.TIMESTAMPTZ型別與LocalDateTime不匹配
異常資訊:
PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type java.time.LocalDateTime.
如果 Postgre s 表的欄位型別是TIMESTAMPTZ ，但是java物件的欄位型別是LocalDateTime， 這時會無法轉換對映上。 Postgre s 表欄位型別應該用timestamp 或者 java欄位型別用Date。
2.參數值不能用雙引號
錯誤例子：
WHERE name = "jay" ===> WHERE name = 'jay'
這裏參數值"jay" 應該改成單引號 'jay'。
3.欄位不能用 `` 包起來
錯誤例子：
WHERE `name` = 'jay' ==> WHERE name = 'jay'
這裏的欄位名name不能用``選取。
4.json欄位處理語法不同
-- MySQL語法: WHERE keywords_json->'$.name' LIKE CONCAT('%', ?, '%')-- postgreSQL語法:WHERE keywords_json ->>'name' LIKE CONCAT('%', ?, '%')
獲取json欄位子內容的值MySQL是用 -> '$.xxx'的語法去選取的， 而 Postgre SQL 得用 ->>'xx' 語法選擇內容。
5.convert函式不存在
Postgre SQL沒有convert函式，用CAST函式替換。
-- MySQL語法: selectconvert(name, DECIMAL(20, 2))-- postgreSQL語法:selectCAST(nameasDECIMAL(20, 2))
6.force index 語法不存在
-- MySQL語法select xx FROMuserforceindex(idx_audit_time)
MySQL可以使用force index強制走索引， Postgre s 沒有，建議去掉。
7.ifnull 函式不存在
Postgre SQL沒有ifnull函式，用COALESCE函式替換。
異常資訊：
cause: org.postgresql.util.PSQLException: ERROR: functionifnull(numeric, numeric) doesnotexist
8.date_format 函式不存在
異常資訊：
Cause: org.postgresql.util.PSQLException: ERROR: functiondate_format(timestampwithouttimezone, unknown) doesnotexist
Postgre SQL沒有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')
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
Postgre SQL的selectd欄位必須是group by的欄位裏的 或者使用了聚合函式。MySQL則沒有這個要求，非聚合列會隨機取值。
錯誤例子：
selectname, age, count(*)fromusergroupby age, score
這時 select name 是錯誤的， 因為group by裏沒有這個欄位，要麽加上，要麽變成select min(name)。
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 endoftransactionblock; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until endoftransactionblock
Postgres資料庫中，同一事務中如果某次資料庫操作中出錯的話，那這個事務以後的資料庫操作都會出錯。正常來說不會有這種情況，但是如果有人去捕獲了事務異常後又去執行資料庫操作就會導致這個問題。MySQL貌似不會有這個問題。
下面就是錯誤的程式碼例子：靠異常去走邏輯。解決辦法就是不要靠資料庫的異常去控制邏輯，手動判斷。
11.型別轉換異常 （大頭）
這個可以說是最坑的， 因為MySQL是支持自動型別轉換的。在表欄位型別和參數值之間如果型別不一樣也會自動進行轉換。而 Postgre SQL是強數據型別，欄位型別和參數值型別之間必須一樣否則就會丟擲異常。
這時候解決辦法一般有兩種：
手動修改程式碼裏的欄位型別和傳參型別保證 或者 Postgre SQL表欄位型別，反正保證雙方一一對應
添加自動隱式轉換函式，達到類似MySQL的效果
布爾值和int型別型別轉換錯誤：
1）select查詢時的轉換異常資訊
Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = boolea
SELECT 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 boolea
updatefrom xx setname = falsewherename = 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 異常 就是在操作符比較的時候有多個轉換邏輯不知道用哪個了，死迴圈了。
三、PostgreSQL輔助指令碼
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 $$;
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 $$;
四、註意事項
1. 將數據表從MySQL遷移 Postgre s 要註意欄位型別要對應不要變更（*）。
2. 原先是 tinyint的就變samllint型別，不要是bool型別，有時程式碼欄位型別可能對應不上。
3. 如果java欄位是LocalDateTime原先MySQL時間型別到 Postgre s 後不要用TIMESTAMPTZ型別。
4. MySQL一般用tinyint型別和java的Boolean欄位對應並且在查詢和更新時支持自動轉換，但是 Postgre s 是強型別不支持，如果想無縫遷移 Postgre s 內部就新增自動轉換的隱式函式，但是缺點是每次部署 Postgre s 後都要去執行一次指令碼。
如果不想這樣，只能修改程式碼的所有表物件的欄位型別和傳參型別保證與 Postgre s 資料庫的欄位型別對應，但是有些依賴的框架底層自己操作資料庫可能就無法修改源碼了，只能修改資料庫表欄位型別了。
作者丨李白的手機
來源丨juejin.cn/post/7356108146632163339
dbaplus社群歡迎廣大技術人員投稿，投稿信箱： [email protected]
