Mysql迁移OpenGauss
公司要做信创的迁移,本文简单描述了一下Mysql迁移到OpenGauss的大概流程步骤
目录
2.2.1得到OpenGauss数据库的ProductName
一、数据库迁移
1.1Mysql、Oracle迁移OpenGauss
1.1.1使用navicat链接openGauss数据库
1.1.1.1选择PostgreSQL
OpenGauss可以使用PostgreSQL的驱动,所以直接选择PostgreSQL就可以。
前提条件:已经建好OpenGauss的初始数据库、模式
1.1.1.2输入连接信息
点击菜单栏工具-数据传输
选择源为Mysql或Oracle
一直下一步即可。
由于迁移后的表中所有字段没有默认值,需要对比Mysql数据库手动添加默认值。
二、通用流程改造
2.1、application.yml的设置
2.1.1JDBC链接
OpenGauss是基于postgresql开发的,可以使用org.postgresql.Driver也可以使用org.opengauss.Driver。
要注意schema的设置
url: jdbc:postgresql://127.0.0.1:5432/数据库名?currentSchema=模式名 |
spring:
datasource:
username: fy
password: *****
url: jdbc:postgresql://XXXXX:5432/fy?currentSchema=fy
driver-class-name: org.postgresql.Driver
hikari:
connection-timeout: 30000
idle-timeout: 60000
max-lifetime: 1800000
minimum-idle: 2
maximum-pool-size: 5
2.1.2activiti的配置
要注意的是database-schema的设置
database-schema: 模式名
spring:
activiti:
db-history-used: true #使用历史表,如果不配置,则工程启动后可以检查数据库,只建立了17张表,历史表没有建立
history-level: full #记录全部历史操作
#自动建表
# flase: 默认值。activiti在启动时,会对比数据库表中保存的版本,如果没有表或者版本不匹配,将抛出异常。
# true: activiti会对数据库中所有表进行更新操作。如果表不存在,则自动创建。
# create_drop: 在activiti启动时创建表,在关闭时删除表(必须手动关闭引擎,才能删除表)。
# drop-create: 在activiti启动时删除原来的旧表,然后在创建新表(不需要手动关闭引擎
check-process-definitions: false # 自动部署验证设置:true-开启(默认)、false-关闭 在resource目录下添加processes文件夹,并且文件夹不能为空
# main:
# allow-bean-definition-overriding: true #当遇到同样名字的时候,是否允许覆盖注册
database-schema: fyjr_sunam #置建表策略,如果没有表,自动创建表 修改这个地方为大写
2.2、databaseIdProvider多数据库配置
2.2.1得到OpenGauss数据库的ProductName
通过下放代码可以得到OpenGauss数据库的ProductName为:PostgreSQL
@Test
public void getDataBaseProductName() throws Exception {
String driver = "org.opengauss.Driver";
String url = "jdbc:opengauss://XXXX:5432/fy?currentSchema=fy";
String username = "fy";
String password = "";
Class.forName(driver);
Connection con = (Connection) DriverManager.getConnection(url,username,password);
String dbVersion = con.getMetaData().getDatabaseProductVersion();
DatabaseMetaData metaData = (DatabaseMetaData) con.getMetaData();
System.out.println("数据库的产品名称:" + metaData.getDatabaseProductName()+" version "+dbVersion);
}
2.2.2增加数据源配置类添加多数据源配置映射
得到opengauss的ProductName后将p.setProperty("PostgreSQL", "opengauss");加到配置中。
其中:
p.setProperty("PostgreSQL", "**");**随便写,**用于xxxMapper.xml中指定databaseId="**",ProductName为PostgreSQL |
@Configuration
@EnableTransactionManagement
@MapperScan({ "com.zzz.yyy.mapper", "com.zzz.yyy.mapper.extend" })
public class MybatisConfig {
@Bean
public DatabaseIdProvider getDatabaseIdProvider() {
DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
Properties p = new Properties();
p.setProperty("MySQL", "mysql");
p.setProperty("Oracle", "oracle");
p.setProperty("PostgreSQL", "opengauss");
p.setProperty("TiDB", "tidb");
p.setProperty("DB2", "db2");
p.setProperty("SQL Server", "sqlserver");
databaseIdProvider.setProperties(p);
return databaseIdProvider;
}
}
2.2.3Mysql语句转OpenGauss
替换步骤:
-
在 xxxMapper.xml 中将所有sql语句上加入 databaseId="mysql"
-
复制一份mysql的sql(即 将替换的opengauss语句)
-
在复制的sql上加入databaseId="opengauss"
-
找出mysql与opengauss语句区别,然后替换sql
凡是PostgreSQL的东西openGauss都兼容。而openGauss扩展了部分语法类似Oracle 的,反过来PostgreSQL无法用。所以为了统一和简化,都使用PostgreSQL的标准。
-
大小写
-
PostgreSQL的数据库内核对大小写敏感。数据库名,数据表名,列名区分大小写。
-
在PostgreSQL中,执行SQL语句时,在不加双引号的情况下,会把所有表示关键字,库名,表名,列名的字符串转换成小写。所以又说PostgreSQL不区分大小写的。
-
在书写SQL时,为了便于理解,默认:关键字大写,表名首字母大写,列名全部小写。
-
-
分页
-
如果从第一条开始取记录 , PostgreSQL与mysql都支持 select * from tb limit A;
-
当从至少第二条数据取记录时 , PostgreSQL仅支持 limit A offset B , 而 mysql 除了支持 pg 的分页之外 , 还支持 limit B,A
-
-
日期转字符串
-
Oracle : To_Char(DATE,'YYYY-MM-DD')
-
PostgreSQL : To_Char(DATE,'YYYY-MM-DD'),同上
-
MySQL : date_format(DATE,'%Y-%m-%d')
-
-
字符串拼接
-
Oracle : Field1 || '_' || Field2 || '_' ||...,也可以用CONCAT()
-
PostgreSQL : Field1 || '_' || Field2 || '_' ||...,也可以用CONCAT(),同上
-
MySQL : CONCAT(Field1,'_',Field2,'_',...)
-
-
字符串转日期
-
Oracle : select to_date('2022-06-01 23:45:59','yyyy-mm-dd hh24:mi:ss') from dual
-
PostgreSQL : select to_timestamp('2022-06-01 23:45:59','yyyy-mm-dd hh24:mi:ss')
-
MySQL : select str_to_date('2022-06-01 23:45:59','%Y-%m-%d %T')
-
-
PostgreSQL和OpenGauss中不能使用“ ` ”符号
以下代码自行修改
package com.zzz.yyy;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;
import java.io.*;
import java.util.*;
/**
* <p> mysql迁移opengauss 测试工具类 </p>
*
* @description :
* @author : zhaoyunlong
* @date : 2023/1/02 10:10
*/
public class MySQLToOracleTest {
private final static String ORACLE_SQL = " <!-- ====================================== ↓↓↓↓↓↓ opengauss ↓↓↓↓↓↓ ====================================== -->";
@Test // 替换项目中的sql语句
public void testSQL() throws Exception {//src/main/resources/mapper
String path = System.getProperty("user.dir") + "\\src\\main\\resources\\mapper"; // TODO 这里替换为自己的项目路径
File file = new File(path);
HashMap<Object, Object> fileMap = new HashMap<>();
getAllFileByRecursion(fileMap, file);
fileMap.forEach((key, value) -> {
String fileXmlName = (String) key;
File fileXml = (File) value;
String filePath = fileXml.getPath();
if (!StringUtils.isEmpty(fileXmlName)) {
System.out.println(filePath);
try {
// 1、加入 databaseId="mysql"
addMysql(filePath);
// 2、复制一份oracle的sql
if (!checkHasOracle(filePath)) {
copyMysqlToOracle(filePath);
}
// 3、加入 databaseId="openss"
addOracle(filePath);
// 4、替换mybatis `xxxMapper.xml` 中的sql语句
repalceSQL(filePath);
} catch (IOException e) {
e.printStackTrace();
}
}
});
System.out.println(fileMap);
}
/**
* 替换mybatis `xxxMapper.xml` 中的sql语句
*/
private static void repalceSQL(String path) throws IOException {
File file = new File(path);
FileReader in = new FileReader(file);
BufferedReader bufIn = new BufferedReader(in);
// 内存流, 作为临时流
CharArrayWriter tempStream = new CharArrayWriter();
// 替换
String line = null;
int row = 0;
int rowOracle = 0;
while ((line = bufIn.readLine()) != null) {
row++;
if (line.contains(ORACLE_SQL)) {
rowOracle = row;
}
if (rowOracle != 0 && row > rowOracle) {
// ① 替换 `LIMIT` -> `AND ROWNUM <= 1` TODO 【注: 部分包含`ORDER BY` 关键字,需单独处理】
// ② oracle中不能使用“ ` ”符号
line = StringUtils.replace(line, "`", "");
// ③ CONCAT('%', #{name}, '%') -> '%'||#{name}||'%' (Oracle中concat函数只能放两个参数)
if (line.contains("concat")) {
// String newLine = line.substring(line.indexOf("concat(") + 7, line.lastIndexOf("'%')") + 3);
line = line.replaceAll(",", " || ");
line = line.replaceAll("concat", "");
}
if (line.contains("CONCAT")) {
// String newLine = line.substring(line.indexOf("CONCAT(") + 7, line.lastIndexOf("'%')") + 3);
line = line.replaceAll(",", " || ");
line = line.replaceAll("CONCAT", "");
}
// ④ `UNIX_TIMESTAMP` -> `ORACLE_TO_UNIX` date类型时间转10位时间戳
line = line.replaceAll("UNIX_TIMESTAMP()", "floor(extract(epoch from now()))");
// ⑤ 部分关键字需加上双引号 TODO 【注: 字段名大写,映射的别名需保存原本小写!】 `level -> "LEVEL"` `user -> "USER"` `number -> "NUMBER"` `desc -> "DESC"`
if (line.endsWith(" date") || line.endsWith(" date,") || line.endsWith(" 'date'") || line.endsWith(" 'DATE'") || line.endsWith("DATE")) {
line = line.replaceAll(" date", " \"date\"");
line = line.replaceAll(" date,", " \"date,\"");
line = line.replaceAll(" 'date'", " \"date\"");
line = line.replaceAll(" 'DATE'", " \"date\"");
line = line.replaceAll(" DATE", " \"date\"");
}
line = line.replaceAll(" date ", " \"date\" ");
line = line.replaceAll(" DATE ", " \"date\" ");
// ⑥ `IFNULL` -> `NVL`
// ⑦ 时间 `str_to_date` -> `to_date` `date_format` -> `to_char`
// `%Y-%m-%d` -> `yyyy-MM-dd` `%Y-%m` -> `yyyy-MM`
line = line.replaceAll("str_to_date", "TO_DATE");
line = line.replaceAll("STR_TO_DATE", "TO_DATE");
line = line.replaceAll("date_format", "TO_CHAR");
line = line.replaceAll("DATE_FORMAT", "TO_CHAR");
// 这里注意替换顺序问题,最长的应该放最前面!!!
line = line.replaceAll("%Y-%m-%d %H:%i:%S", "yyyy-MM-dd HH24:mi:ss");
line = line.replaceAll("%Y-%m-%d %H:%i:%s", "yyyy-MM-dd HH24:mi:ss");
line = line.replaceAll("%Y-%m-%d %H:%i", "yyyy-MM-dd HH24:mi");
line = line.replaceAll("%Y-%m-%d %H", "yyyy-MM-dd HH24");
line = line.replaceAll("%Y-%m-%d %h", "yyyy-MM-dd HH");
line = line.replaceAll("%Y-%m-%d", "yyyy-MM-dd");
line = line.replaceAll("%Y-%m", "yyyy-MM");
line = line.replaceAll("%Y", "yyyy");
line = line.replaceAll("%H", "HH24");
line = line.replaceAll("%k", "HH24");
line = line.replaceAll("now\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");
line = line.replaceAll("NOW\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");
// ⑧ ...
// 需手动处理的SQL 【 group by | 批量插入 | ... 】
}
// 将该行写入内存
tempStream.write(line);
// 添加换行符
tempStream.append(System.getProperty("line.separator"));
}
// 关闭 输入流
bufIn.close();
// 将内存中的流 写入 文件
FileWriter out = new FileWriter(file);
tempStream.writeTo(out);
out.close();
}
/**
* 加入 databaseId="mysql"
*/
private static void addMysql(String path) throws IOException {
File file = new File(path);
FileReader in = new FileReader(file);
BufferedReader bufIn = new BufferedReader(in);
// 内存流, 作为临时流
CharArrayWriter tempStream = new CharArrayWriter();
// 替换
String line = null;
while ((line = bufIn.readLine()) != null) {
if ((line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) && !line.contains("databaseId")) {
if (line.endsWith(">")) {
line = line.replaceAll(">", " databaseId=\"mysql\">");
} else {
line = line + " databaseId=\"mysql\"";
}
}
// 将该行写入内存
tempStream.write(line);
// 添加换行符
tempStream.append(System.getProperty("line.separator"));
}
// 关闭 输入流
bufIn.close();
// 将内存中的流 写入 文件
FileWriter out = new FileWriter(file);
tempStream.writeTo(out);
out.close();
}
/**
* 加入 databaseId="oracle"
*/
private static void addOracle(String path) throws IOException {
File file = new File(path);
FileReader in = new FileReader(file);
BufferedReader bufIn = new BufferedReader(in);
// 内存流, 作为临时流
CharArrayWriter tempStream = new CharArrayWriter();
HashSet<String> lineSet = new HashSet<>();
// 替换
String line = null;
while ((line = bufIn.readLine()) != null) {
if (line.contains("databaseId=\"mysql\"")) {
if (lineSet.contains(line)) {
line = line.replaceAll("databaseId=\"mysql\"", "databaseId=\"opengauss\"");
}
lineSet.add(line);
}
// 将该行写入内存
tempStream.write(line);
// 添加换行符
tempStream.append(System.getProperty("line.separator"));
}
// 关闭 输入流
bufIn.close();
// 将内存中的流 写入 文件
FileWriter out = new FileWriter(file);
tempStream.writeTo(out);
out.close();
}
/**
* 复制一份oracle的sql
*/
private static void copyMysqlToOracle(String path) throws IOException {
File file = new File(path);
FileReader in = new FileReader(file);
BufferedReader bufIn = new BufferedReader(in);
// 内存流, 作为临时流
CharArrayWriter tempStream = new CharArrayWriter();
// 替换
String line = null;
// 需要替换的行
List<String> lineList = new LinkedList<>();
int row = 0;
int firstRow = 0;
while ((line = bufIn.readLine()) != null) {
row++;
if (line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) {
firstRow = row;
}
// 添加替换内容
if (firstRow != 0 && row >= firstRow && !line.contains("</mapper>") && !line.contains("<!-- ========") && !line.contains("oracle")) {
lineList.add(line);
}
// 查询结束位置
if (line.contains("<!-- =======")) {
tempStream.append(System.getProperty("line.separator"));
tempStream.write(ORACLE_SQL);
tempStream.append(System.getProperty("line.separator"));
tempStream.append(System.getProperty("line.separator"));
lineList.forEach(lineValue -> {
// copy mysql 语句 转为oracle
try {
tempStream.write(lineValue);
tempStream.append(System.getProperty("line.separator"));
} catch (IOException e) {
e.printStackTrace();
}
});
tempStream.append(System.getProperty("line.separator"));
}
// 将该行写入内存
tempStream.write(line);
// 添加换行符
tempStream.append(System.getProperty("line.separator"));
}
// 关闭 输入流
bufIn.close();
// 将内存中的流 写入 文件
FileWriter out = new FileWriter(file);
tempStream.writeTo(out);
out.close();
}
/**
* 检查是否已经复制SQL
*/
private static boolean checkHasOracle(String path) throws IOException {
File file = new File(path);
FileReader in = new FileReader(file);
BufferedReader bufIn = new BufferedReader(in);
// 内存流, 作为临时流
CharArrayWriter tempStream = new CharArrayWriter();
// 替换
String line = null;
boolean result = false;
while ((line = bufIn.readLine()) != null) {
if (line.contains(ORACLE_SQL)) {
result = true;
}
// 将该行写入内存
tempStream.write(line);
// 添加换行符
tempStream.append(System.getProperty("line.separator"));
}
// 关闭 输入流
bufIn.close();
// 将内存中的流 写入 文件
FileWriter out = new FileWriter(file);
tempStream.writeTo(out);
out.close();
return result;
}
/**
* 递归文件夹 -> 找到所有xml文件
*/
private static void getAllFileByRecursion(HashMap<Object, Object> fileMap, File file) {
File[] fs = file.listFiles();
for (File f : fs) {
String fileName = f.getName();
if (f.isDirectory()) {
// 若是目录则递归,否则打印该目录下的文件
getAllFileByRecursion(fileMap, f);
}
if (f.isFile() && fileName.endsWith(".xml")) {
fileMap.put(fileName, f);
}
}
}
}
参考:
(68条消息) 数据库国产化——mybatis完成多数据源配置(兼容达梦、高斯)简记_mybatis多数据库兼容_shark-chili的博客-CSDN博客
(68条消息) 业务系统兼容数据库Oracle/PostgreSQL(openGauss)/MySQL的琐事_oracle goldendb_若苗瞬的博客-CSDN博客
更多推荐
所有评论(0)