項(xiàng)目從 MySQL 切換 PostgreSQL，踩了太多的坑！??！

0、前言

原項(xiàng)目框架 SpringBoot + MybatisPlus + Mysql

1、切換流程

1.1、項(xiàng)目引入postgresql驅(qū)動(dòng)包

由于我們要連接新的數(shù)據(jù)庫(kù)，理所當(dāng)然的要引入該數(shù)據(jù)庫(kù)的驅(qū)動(dòng)包，這與mysql驅(qū)動(dòng)包類似

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

1.2、修改jdbc連接信息

之前用的是mysql協(xié)議，現(xiàn)在改成postgresql連接協(xié)議

spring:
  datasource:
    # 修改驅(qū)動(dòng)類
    driver-class-name: org.postgresql.Driver
    # 修改連接地址
    url: jdbc:postgresql://數(shù)據(jù)庫(kù)地址/數(shù)據(jù)庫(kù)名?currentSchema=模式名&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

postgres相比mysql多了一層模式的概念， 一個(gè)數(shù)據(jù)庫(kù)下可以有多個(gè)模式。這里的模型名等價(jià)于以前的mysql的數(shù)據(jù)庫(kù)名。如果不指定默認(rèn)是public。

這時(shí)切換流程基本就改造完了，無(wú)非就是代碼修改下連接信息。但是你以為到這就結(jié)束了？

一堆坑還在后面呢，畢竟是兩個(gè)完全不同數(shù)據(jù)庫(kù)在語(yǔ)法層面還有很多差別，接下來(lái)就是修改代碼里的sql語(yǔ)法踩坑

2、踩坑記錄

2.1、TIMESTAMPTZ類型與LocalDateTime不匹配

異常信息:

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

如果postgres表的字段類型是TIMESTAMPTZ ，但是java對(duì)象的字段類型是LocalDateTime， 這時(shí)會(huì)無(wú)法轉(zhuǎn)換映射上。postgres表字段類型應(yīng)該用timestamp 或者 java字段類型用Date

2.2、參數(shù)值不能用雙引號(hào)

錯(cuò)誤例子:

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

這里參數(shù)值"jay" 應(yīng)該改成單引號(hào) 'jay'

2.3、字段不能用``包起來(lái)

錯(cuò)誤例子

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

這里的字段名name不能用``選取

2.4、json字段處理語(yǔ)法不同

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

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

獲取json字段子屬性的值mysql是用 -> '$.xxx'的語(yǔ)法去選取的， 而 postgreSQL 得用 ->>'xx' 語(yǔ)法選擇屬性

2.5、convert函數(shù)不存在

postgreSQL沒(méi)有convert函數(shù)，用CAST函數(shù)替換

-- mysql語(yǔ)法: 
select convert(name, DECIMAL(20, 2))

-- postgreSQL語(yǔ)法:
select CAST(name as DECIMAL(20, 2))

2.6、force index 語(yǔ)法不存在

-- mysql語(yǔ)法
select xx FROM user force index(idx_audit_time)

mysql可以使用force index強(qiáng)制走索引， postgres沒(méi)有，建議去掉

2.7、ifnull 函數(shù)不存在

postgreSQL沒(méi)有ifnull函數(shù)，用COALESCE函數(shù)替換

異常信息

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

2.8、date_format 函數(shù)不存在

異常信息

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

postgreSQL沒(méi)有date_format函數(shù)，用to_char函數(shù)替換

替換例子:

// %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語(yǔ)法問(wèn)題

異常信息

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的字段里的 或者使用了聚合函數(shù)。mysql則沒(méi)有這個(gè)要求，非聚合列會(huì)隨機(jī)取值

錯(cuò)誤例子

select name, age, count(*)
from user 
group by age, score

這時(shí) select name 是錯(cuò)誤的， 應(yīng)為group by里沒(méi)有這個(gè)字段，要么加上，要么變成select min(name)

2.10、事務(wù)異常問(wèn)題

異常信息

# 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數(shù)據(jù)庫(kù)中，同一事務(wù)中如果某次數(shù)據(jù)庫(kù)操作中出錯(cuò)的話，那這個(gè)事務(wù)以后的數(shù)據(jù)庫(kù)操作都會(huì)出錯(cuò)。正常來(lái)說(shuō)不會(huì)有這種情況，但是如果有人去捕獲了事務(wù)異常后又去執(zhí)行數(shù)據(jù)庫(kù)操作就會(huì)導(dǎo)致這個(gè)問(wèn)題。mysql貌似不會(huì)有這個(gè)問(wèn)題

下面就是錯(cuò)誤的代碼例子：靠異常去走邏輯。解決辦法就是不要靠數(shù)據(jù)庫(kù)的異常去控制邏輯，手動(dòng)判斷。

2.11 類型轉(zhuǎn)換異常 （大頭）

這個(gè)可以說(shuō)是最坑的， 因?yàn)閙ysql是支持自動(dòng)類型轉(zhuǎn)換的。在表字段類型和參數(shù)值之間如果類型不一樣也會(huì)自動(dòng)進(jìn)行轉(zhuǎn)換。而postgreSQL是強(qiáng)數(shù)據(jù)類型，字段類型和參數(shù)值類型之間必須一樣否則就會(huì)拋出異常。

這時(shí)候解決辦法一般有兩種

• 手動(dòng)修改代碼里的字段類型和傳參類型保證 或者 postgreSQL表字段類型，反正保證雙方一一對(duì)應(yīng)
• 添加自動(dòng)隱式轉(zhuǎn)換函數(shù)，達(dá)到類似mysql的效果

布爾值和int類型類型轉(zhuǎn)換錯(cuò)誤

1、select查詢時(shí)的轉(zhuǎn)換異常信息

Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = boolean
SELECT   xx fom xx    WHERE   enable = ture

錯(cuò)誤原因：enable字段是smallint類型查詢卻傳了一個(gè)布爾值類型

2、update更新時(shí)的轉(zhuǎn)換異常信息

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

錯(cuò)誤原因：在update/insert賦值語(yǔ)句的時(shí)候，字段類型是smallint，但是傳參卻是布爾值類型

解決辦法：

postgres數(shù)據(jù)庫(kù)添加boolean <-> smallint 的自動(dòng)轉(zhuǎn)換邏輯

-- 創(chuàng)建函數(shù)1  smallint到boolean到轉(zhuǎn)換函數(shù)
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
-- 創(chuàng)建賦值轉(zhuǎn)換1
create cast (SMALLINT as BOOLEAN) with function smallint_to_boolean as ASSIGNMENT;

-- 創(chuàng)建函數(shù)2    boolean到smallint到轉(zhuǎn)換函數(shù)
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
  
-- 創(chuàng)建隱式轉(zhuǎn)換2
create cast (BOOLEAN as SMALLINT) with function boolean_to_smallint as implicit;

如果想重來(lái)可以刪除掉上面創(chuàng)建的函數(shù)和轉(zhuǎn)換邏輯

-- 刪除函數(shù)
drop function smallint_to_boolean
-- 刪除轉(zhuǎn)換
drop  CAST (SMALLINT as BOOLEAN)

主要不要亂添加隱式轉(zhuǎn)換函數(shù)，可能導(dǎo)致  Could not choose a best candidate operator 異常 和 # operator is not unique 異常 就是在操作符比較的時(shí)候有多個(gè)轉(zhuǎn)換邏輯不知道用哪個(gè)了，死循環(huán)了

3、PostgreSQL輔助腳本

3.1、批量修改timestamptz腳本

批量修改表字段類型 timestamptz 為 timestamp， 因?yàn)槲覀冋f(shuō)過(guò)前者無(wú)法與LocalDateTime對(duì)應(yīng)上

?

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、批量設(shè)置時(shí)間默認(rèn)值腳本

批量修改模式名下的所有字段類型為timestamp的并且字段名為 create_time 或者 update_time的字段的默認(rèn)值為 CURRENT_TIMESTAMP

-- 注意 || 號(hào)拼接的后面的字符串前面要有一個(gè)空格
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、注意事項(xiàng)

1、將數(shù)據(jù)表從mysql遷移postgres 要注意字段類型要對(duì)應(yīng)不要變更（*）

2、原先是 tinyint的就變samllint類型，不要是bool類型，有時(shí)代碼字段類型可能對(duì)應(yīng)不上

3、如果java字段是LocalDateTime原先mysql時(shí)間類型到postgres后不要用TIMESTAMPTZ類型

4、mysql一般用tinyint類型和java的Boolean字段對(duì)應(yīng)并且在查詢和更新時(shí)支持自動(dòng)轉(zhuǎn)換，但是postgres是強(qiáng)類型不支持，如果想無(wú)縫遷移postgres內(nèi)部就新增自動(dòng)轉(zhuǎn)換的隱式函數(shù)，但是缺點(diǎn)是每次部署postgres后都要去執(zhí)行一次腳本。

如果不想這樣，只能修改代碼的所有表對(duì)象的字段類型和傳參類型保證與postgres數(shù)據(jù)庫(kù)的字段類型對(duì)應(yīng)，但是有些依賴的框架底層自己操作數(shù)據(jù)庫(kù)可能就無(wú)法修改源碼了，只能修改數(shù)據(jù)庫(kù)表字段類型了