MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享（通过bat命令修改mapper文件内的SQL语法） |
您所在的位置：网站首页 › mysql调整字段位置 › MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享（通过bat命令修改mapper文件内的SQL语法） |
|
我正在参加「掘金·启航计划」 网盘内容PowerShell-7.2.6-win-x64.msi 链接：pan.baidu.com/s/1xS5muiiw… 提取码：u1ln 1.迁移说明数据架构方案由现在的GP+MySQL调整为GP/PG。新的方案中，当使用单服务器部署数据库时，数据库直接部署为PG，可以使用Docker进行快速部署避免GP部署麻烦的问题，且适用于大多数的客户使用场景；当PG无法承载客户的数据时，部署方案调整为GP，相对的部署方案会复杂一些，同时依然要部署PG数据库用来作为其他微服务的业务数据库，避免GP的高并发不适应的问题，提高系统的稳定性。尽量不使用PG9到PG12中间新增的特性函数用法，查询语句几乎兼容PG和GP。 PG数据库的语法与MySQL不完全相同，故需要对SQL进行调整，且PG对字段类型的匹配也比较严格，需要对其进行强制转换。 2.函数 2.1 字符串函数 函数说明返回类型string || string字符串连接textlength(string)字符串中字符的数目intlower(str)、upper(str)字符串小写与大写textposition(substring in string)字符串在另一个字符串中出现的位置intsubstring(string [from int] [for int])获取子字符串texttrim([leading | trailing | both] [characters] from string)从字符串的开头|结尾|两边截掉指定字符textltrim(string)、rtrim(string)、trim(string)截掉字符串开头|结尾|两边的空格textoverlay(string placing string from int [for int])替换指定位置的字符textreplace(string text, from text, to text)替换所有子字符为另一个字符textsplit_part(string text, delimiter text, field int)根据分隔字符返回第field个字符串textstring_agg(fields, delimiter text [order by fields])分组拼接字符串text部分实例： -- 字符串连接 -- %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) 2.2 日期函数 函数说明返回类型current_date当前日期datecurrent_time当前时间time with time zonecurrent_timestamp、now()当前时间戳timestamp with time zonedate_part(text, timestamp)获取子域（等效于extract）double precisionextract(unit from date)获取子域double precisiondate_trunc(text, timestamp)截取指定的精度timestamp部分实例： -- 获取当前的日期、时间、时间戳 -- 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) 2.3 其他函数 -- 根据分组将字段值放入array并获取排序后的值 SELECT ( ARRAY_AGG ( fields order by fieldsName desc ) ) [ 1 ] FROM table 3.主要修改 3.1字段别名 -- 字段别名（需要添加双引号 否则会变小写） SELECT fields_name AS "fieldsName" FROM table_name 3.2 concat -- MySQL concat('%', #{keyword}, '%') -- PostgreSQL '%' || #{keyword} || '%' 4.替换语句数据库需要安装以下扩展： -- 用于uuid函数 CREATE extension "uuid-ossp";以下使用bat脚本进行全量替换： 原始值替换值STR_TO_DATETO_TIMESTAMPstr_to_dateto_timestamp%Y-%m-%d %H:%i:%sYYYY-MM-DD hh24:mi:ss%Y-%m-%dYYYY-MM-DD%H:%i:%shh24:mi:ss%Y-%mYYYY-MMdate_formatto_charsysdate()to_timestamp ( '' || now( ), 'YYYY-MM-DD hh24:mi:ss' )uuid()uuid_generate_v4()IFNULLCOALESCEifnullcoalesce 5.脚本分享 5.1 脚本内容 @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 " ) 5.2 脚本使用说明 需要安装最新版本的powershell否在会出现中文乱码（主要是mapper文件内的注释信息）。 将bat脚本放置到mpper文件夹下。 双击执行。（会批量替换一部分函数和字符串） |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |