項目從 MySQL 切換 PostgreSQL，踩了太多的坑！！！
0、前言
原項目框架 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://數據庫地址/數據庫名?currentSchema=模式名&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=falsepostgres相比mysql多了一層模式的概念， 一個數據庫下可以有多個模式。這里的模型名等價于以前的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 exist2.8、date_format 函數不存在
異常信息
Cause: org.postgresql.util.PSQLException: ERROR: function date_format(timestamp without time zone, unknown) does not existpostgreSQL沒有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 functionpostgreSQL 的 selectd的字段必須是group by的字段里的 或者使用了聚合函數。mysql則沒有這個要求，非聚合列會隨機取值
錯誤例子
select name, age, count(*)
from user
group by 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 blockPostgres數據庫中，同一事務中如果某次數據庫操作中出錯的話，那這個事務以后的數據庫操作都會出錯。正常來說不會有這種情況，但是如果有人去捕獲了事務異常后又去執行數據庫操作就會導致這個問題。mysql貌似不會有這個問題
下面就是錯誤的代碼例子：靠異常去走邏輯。解決辦法就是不要靠數據庫的異常去控制邏輯，手動判斷。
圖片
2.11、類型轉換異常 （大頭）
這個可以說是最坑的， 因為mysql是支持自動類型轉換的。在表字段類型和參數值之間如果類型不一樣也會自動進行轉換。而postgreSQL是強數據類型，字段類型和參數值類型之間必須一樣否則就會拋出異常。
這時候解決辦法一般有兩種
- 手動修改代碼里的字段類型和傳參類型保證 或者 postgreSQL表字段類型，反正保證雙方一一對應
- 添加自動隱式轉換函數，達到類似mysql的效果
布爾值和int類型類型轉換錯誤
1、select查詢時的轉換異常信息
Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = boolean
SELECT xx fom xx WHERE enable = ture錯誤原因：enable字段是smallint類型查詢卻傳了一個布爾值類型
2、update更新時的轉換異常信息
Cause: org.postgresql.util.PSQLException: ERROR: column "name" is of type smallint but expression is of type boolean
update from xx set name = false where name = true錯誤原因：在update/insert賦值語句的時候，字段類型是smallint，但是傳參卻是布爾值類型
解決辦法：
postgres數據庫添加boolean <-> smallint 的自動轉換邏輯
-- 創建函數1 smallint到boolean到轉換函數
CREATE OR REPLACE FUNCTION "smallint_to_boolean"("i" int2)
RETURNS "pg_catalog"."bool" AS $BODY$
BEGIN
RETURN (i::int2)::integer::bool;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
-- 創建賦值轉換1
create cast (SMALLINT as BOOLEAN) with function smallint_to_boolean as ASSIGNMENT;
-- 創建函數2 boolean到smallint到轉換函數
CREATE OR REPLACE FUNCTION "boolean_to_smallint"("b" bool)
RETURNS "pg_catalog"."int2" AS $BODY$
BEGIN
RETURN (b::boolean)::bool::int;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
-- 創建隱式轉換2
create cast (BOOLEAN as SMALLINT) with function boolean_to_smallint as implicit;如果想重來可以刪除掉上面創建的函數和轉換邏輯
-- 刪除函數
drop function smallint_to_boolean
-- 刪除轉換
drop CAST (SMALLINT as BOOLEAN)主要不要亂添加隱式轉換函數，可能導致 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;
BEGIN
FOR rec IN SELECT table_name, column_name,data_type
FROM information_schema.columns
where table_schema = '要處理的模式名'
AND data_type = 'timestamp with time zone'
LOOP
EXECUTE 'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' TYPE timestamp';
END LOOP;
END $$;3.2、批量設置時間默認值腳本
批量修改模式名下的所有字段類型為timestamp的并且字段名為 create_time 或者 update_time的字段的默認值為 CURRENT_TIMESTAMP
-- 注意 || 號拼接的后面的字符串前面要有一個空格
DO $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT table_name, column_name,data_type
FROM information_schema.columns
where table_schema = '要處理的模式名'
AND data_type = 'timestamp without time zone'
-- 修改的字段名
and column_name in ('create_time','update_time')
LOOP
EXECUTE 'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' SET DEFAULT CURRENT_TIMESTAMP;';
END LOOP;
END $$;4、注意事項
- 將數據表從mysql遷移postgres 要注意字段類型要對應不要變更（
*）。 - 原先是
tinyint的就變samllint類型，不要是bool類型，有時代碼字段類型可能對應不上。 - 如果java字段是
LocalDateTime原先mysql時間類型到postgres后不要用TIMESTAMPTZ類型。 - mysql一般用tinyint類型和java的Boolean字段對應并且在查詢和更新時支持自動轉換，但是postgres是強類型不支持，如果想無縫遷移postgres內部就新增自動轉換的隱式函數，但是缺點是每次部署postgres后都要去執行一次腳本。
如果不想這樣，只能修改代碼的所有表對象的字段類型和傳參類型保證與postgres數據庫的字段類型對應，但是有些依賴的框架底層自己操作數據庫可能就無法修改源碼了，只能修改數據庫表字段類型了。
