MySQL【实践 02】MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享（通过bat命令修改mapper文件内的SQL语法）

-- 字符串连接
-- %keyword%
SELECT '%' || 'keyword' || '%' 
-- 字符串中字符的数目
-- 15
SELECT LENGTH( 'PostgreSQL12数据库' )
-- 字符串小写与大写
-- postgresql,POSTGRESQL
SELECT LOWER( 'PostgreSQL' ),UPPER( 'PostgreSQL' )
-- 字符串出现的位置（下标从1开始）
-- 8
SELECT POSITION( 'S' IN 'PostgreSQL' );
-- 获取子字符串（下标从1开始）
-- SQL
SELECT SUBSTRING( 'PostgreSQL' FROM 8 FOR 3 );
-- 删除字符串的指定字符
-- PostgreSQL
SELECT TRIM( BOTH 's' FROM 'sPostgreSQLss' );
-- 删除字符两端空格
-- PostgreSQL
SELECT TRIM( '  PostgreSQL  ' )
-- 替换指定位置的字符
-- PostgreSQL
SELECT OVERLAY( 'PxxxgreSQL' PLACING 'ost' FROM 2 FOR 3 );
-- 替换所有子字符为另一个字符
-- PostgreSQL
SELECT REPLACE( 'PostgresQL', 'sQ', 'SQ' );
-- 根据分隔字符返回第x个字符串
-- PostgreSQL
SELECT SPLIT_PART( 'MySQL|PostgreSQL|Greenplum', '|', 2 );
-- 分组拼接字符串 类似与 group_concat
STRING_AGG(fields, ',' )
STRING_AGG(fields, ',' ORDER BY fields)

-- 获取当前的日期、时间、时间戳
-- 2022-10-19,15:22:38.890969+08,2022-10-19 15:22:38.890969+08,2022-10-19 15:22:38.890969+08
SELECT CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,now()
-- 获取子域 year,month,day,hour,minute,second
-- 2022
SELECT date_part('year',TIMESTAMP'2022-10-19 11:07:30') 
-- 10
SELECT extract('month' FROM now())
-- 截取指定的精度
-- now() 2022-10-19 16:43:17.895054+08
-- 2022-10-19 00:00:00+08
SELECT date_trunc('day', now());
-- 2022-10-19 16:00:00+08
SELECT date_trunc('hour', now());

-- 日期加减 year,month,day,hour,minute,second
-- 2022-10-19 13:51:00.409176+08 --> 2022-11-18 13:51:00.409176+08
SELECT now( ) + INTERVAL '1 month' - INTERVAL '1 day'
-- 字符串转时间戳
-- 2022-10-19 16:53:12+08
SELECT to_timestamp( '2022-10-19 16:53:12', 'YYYY-MM-DD hh24:mi:ss' )
-- 日期转字符串
-- 2022-10-19 16:52:28
SELECT to_char ( now( ), 'YYYY-MM-DD hh24:mi:ss' )
-- 2018-12-06
SELECT to_char ( CAST (''|| 20181206 AS TIMESTAMP ), 'YYYY-MM-DD' )
-- 空值函数（默认值的类型要跟字段的值类型一致）
SELECT COALESCE( fieldsName, fieldsDefaultValue)

-- 根据分组将字段值放入array并获取排序后的值
SELECT ( ARRAY_AGG ( fields order by fieldsName desc ) ) [ 1 ] FROM table

-- 字段别名（需要添加双引号 否则会变小写）
SELECT fields_name AS "fieldsName" FROM table_name

-- MySQL
concat('%', #{keyword}, '%')
-- PostgreSQL
'%' || #{keyword} || '%'

-- 用于uuid函数
CREATE extension "uuid-ossp";

@echo off
setlocal EnableDelayedExpansion
:: (1)替换 STR_TO_DATE 函数为 TO_TIMESTAMP
set "strOld1=STR_TO_DATE"
set "strNew1=TO_TIMESTAMP"
echo (1)Replac: STR_TO_DATE TO TO_TIMESTAMP
:: (2)替换 str_to_date 函数为 to_timestamp
set "strOld2=str_to_date"
set "strNew2=to_timestamp"
echo (2)Replac: str_to_date TO to_timestamp
for /f %%i in ('dir /b /s /a:-d *.xml') do (
 pwsh -Command "(gc %%i) -replace '%strOld1%', '%strNew1%' -replace '%strOld2%', '%strNew2%' | Out-File %%i -Encoding utf8 "
)
:: (3)替换日期格式 %Y-%m-%d %H:%i:%s 为 YYYY-MM-DD hh24:mi:ss
set "strOld1=%%Y-%%m-%%d %%H:%%i:%%s"
set "strNew1=YYYY-MM-DD hh24:mi:ss"
echo (3)Replac: %%Y-%%m-%%d %%H:%%i:%%s TO YYYY-MM-DD hh24:mi:ss
:: (4)替换日期格式 %Y-%m-%d 为 YYYY-MM-DD
set "strOld2=%%Y-%%m-%%d"
set "strNew2=YYYY-MM-DD"
echo (4)Replac: %%Y-%%m-%%d TO YYYY-MM-DD
:: (5)替换日期格式 %H:%i:%s 为 hh24:mi:ss
set "strOld3=%%H:%%i:%%s"
set "strNew3=hh24:mi:ss"
echo (5)Replac: %%H:%%i:%%s TO hh24:mi:ss
:: (6)替换日期格式 %Y-%m 为 YYYY-MM
set "strOld4=%%Y-%%m"
set "strNew4=YYYY-MM"
echo (6)Replac: %%Y-%%m TO YYYY-MM
for /f %%i in ('dir /b /s /a:-d *.xml') do (
 pwsh -Command "(gc %%i) -replace '!strOld1!', '%strNew1%' -replace '!strOld2!', '%strNew2%' -replace '!strOld3!', '%strNew3%' -replace '!strOld4!', '%strNew4%'| Out-File %%i -Encoding utf8 "
)
:: (7)替换 date_format 函数为 to_char
set "strOld=date_format"
set "strNew=to_char"
echo (7)Replac: date_format TO to_char
for /f %%i in ('dir /b /s /a:-d *.xml') do (
 pwsh -Command "(gc %%i) -replace '%strOld%', '%strNew%' | Out-File %%i -Encoding utf8 "
)
:: (8)替换 sysdate() 函数为 to_timestamp ( '' || now( ), 'YYYY-MM-DD hh24:mi:ss' )
set "strOld=sysdate\(\)"
set "strNew=to_timestamp(now() || '''',''YYYY-MM-DD hh24:mi:ss'')"
echo (8)Replac: "sysdate() TO to_timestamp (now()||'', 'YYYY-MM-DD hh24:mi:ss')"
for /f %%i in ('dir /b /s /a:-d *.xml') do (
 pwsh -Command "(gc %%i) -replace '!strOld!', '!strNew!' | Out-File %%i -Encoding utf8 "
)
:: (9)替换 uuid 函数为 uuid_generate_v4
set "strOld=uuid\(\)"
set "strNew=%''''|| uuid_generate_v4()"
echo (9)Replac: uuid TO uuid_generate_v4
for /f %%i in ('dir /b /s /a:-d *.xml') do (
 pwsh -Command "(gc %%i) -replace '!strOld!', '!strNew!' | Out-File %%i -Encoding utf8 "
)
:: (10)替换 IFNULL 函数为 COALESCE
set "strOld1=ifnull"
set "strNew1=coalesce"
set "strOld2=IFNULL"
set "strNew2=COALESCE"
echo (10)Replac: IFNULL To COALESCE
for /f %%i in ('dir /b /s /a:-d *.xml') do (
 pwsh -Command "(gc %%i) -replace '%strOld1%', '%strNew1%' -replace '%strOld2%', '%strNew2%' | Out-File %%i -Encoding utf8 "
)