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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL【实践 02】MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享(通过bat命令修改mapper文件内的SQL语法)

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 字符串连接 text
length(string) 字符串中字符的数目 int
lower(str)、upper(str) 字符串小写与大写 text
position(substring in string) 字符串在另一个字符串中出现的位置 int
substring(string [from int] [for int]) 获取子字符串 text
trim([leading | trailing | both] [characters] from string) 从字符串的开头|结尾|两边截掉指定字符 text
ltrim(string)、rtrim(string)、trim(string) 截掉字符串开头|结尾|两边的空格 text
overlay(string placing string from int [for int]) 替换指定位置的字符 text
replace(string text, from text, to text) 替换所有子字符为另一个字符 text
split_part(string text, delimiter text, field int) 根据分隔字符返回第field个字符串 text
string_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 当前日期 date
current_time 当前时间 time with time zone
current_timestamp、now() 当前时间戳 timestamp with time zone
date_part(text, timestamp) 获取子域(等效于extract) double precision
extract(unit from date) 获取子域 double precision
date_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_DATE TO_TIMESTAMP
str_to_date to_timestamp
%Y-%m-%d %H:%i:%s YYYY-MM-DD hh24:mi:ss
%Y-%m-%d YYYY-MM-DD
%H:%i:%s hh24:mi:ss
%Y-%m YYYY-MM
date_format to_char
sysdate() to_timestamp ( ‘’ || now( ), ‘YYYY-MM-DD hh24:mi:ss’ )
uuid() uuid_generate_v4()
IFNULL COALESCE
ifnull coalesce

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 脚本使用说明

  1. 需要安装最新版本的powershell否在会出现中文乱码(主要是mapper文件内的注释信息)。
  2. 将bat脚本放置到mpper文件夹下。
  3. 双击执行。(会批量替换一部分函数和字符串)
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
12天前
|
SQL 数据可视化 关系型数据库
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
|
12天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
|
12天前
|
关系型数据库 MySQL 数据库
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
|
14天前
|
存储 关系型数据库 MySQL
数据库字符编码MySQL中使用UTF-8还是UTFB4
数据库字符编码MySQL中使用UTF-8还是UTFB4
18 0
|
12天前
|
SQL 关系型数据库 MySQL
轻松入门MySQL:深入学习数据库表管理,创建、修改、约束、建议与性能优化(3)
轻松入门MySQL:深入学习数据库表管理,创建、修改、约束、建议与性能优化(3)
|
1天前
|
存储 关系型数据库 MySQL
【后端面经】【数据库与MySQL】为什么MySQL用B+树而不用B树?-02
【4月更文挑战第11天】数据库索引使用规则:`AND`用`OR`不用,正用反不用,范围中断。索引带来空间和内存代价,包括额外磁盘空间、内存占用和数据修改时的维护成本。面试中可能涉及B+树、聚簇索引、覆盖索引等知识点。MySQL采用B+树,因其利于范围查询和内存效率。数据库不使用索引可能因`!=`、`LIKE`、字段区分度低、特殊表达式或全表扫描更快。索引与NULL值处理在不同数据库中有差异,MySQL允许NULL在索引中的使用。
8 3
|
3天前
|
关系型数据库 MySQL 数据库连接
Django(四):Django项目部署数据库及服务器配置详解(MySQL)
Django(四):Django项目部署数据库及服务器配置详解(MySQL)
25 11
|
3天前
|
运维 关系型数据库 分布式数据库
「合肥 * 讯飞」4 月 19 日 PolarDB 开源数据库沙龙,报名中!
4月19日周五,PolarDB开源社区联合科大讯飞共同举办开源数据库技术沙龙,本次沙龙我们邀请了众多数据库领域的专家,期待大家的参与!
「合肥 * 讯飞」4 月 19 日 PolarDB 开源数据库沙龙,报名中!
|
7天前
|
关系型数据库 MySQL 数据库
mysql卸载、下载、安装(window版本)
mysql卸载、下载、安装(window版本)
|
1月前
|
关系型数据库 MySQL 数据库连接
关于MySQL-ODBC的zip包安装方法
关于MySQL-ODBC的zip包安装方法