mysql7.x脚本转H2嵌入式数据库，这里脚本指的是使用Navicat、dbeaver 导出的mysql脚本
依赖
<dependency>
<groupId>com.mybatis-flex</groupId>
<artifactId>mybatis-flex-core</artifactId>
<version>1.11.0</version>
</dependency>
<dependency>
<groupId>com.github.chris2018998</groupId>
<artifactId>beecp</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.3.232</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-core</artifactId>
<version>5.8.39</version>
</dependency>
代码
准备好 mysql导出脚本，这里脚本指的是使用Navicat、dbeaver 导出的mysql脚本
package top.lingkang.ma.db;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.Assert;
import com.mybatisflex.core.MybatisFlexBootstrap;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.h2.Driver;
import org.stone.beecp.BeeDataSource;
import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.nio.charset.StandardCharsets;
import java.nio.file.Paths;
import java.sql.Connection;
import java.util.Arrays;
import java.util.List;
import static cn.dev33.satoken.SaManager.log;
/**
* mysql7.x脚本转H2嵌入式数据库，这里脚本指的是使用Navicat、dbeaver 导出的mysql脚本
* <pre>{@code
* public static void main(String[] args) {
* Mysql7ScriptToH2 mysql7ScriptToH2 = new Mysql7ScriptToH2(
* Paths.get(System.getProperty("user.dir"), "sql/dump-mini-202509140144.sql").toFile().getAbsolutePath(),
* "jdbc:h2:C:\\Users\\Administrator\\Desktop\\temp\\testH2",
* null, null
* );
* mysql7ScriptToH2.transform();
* }
* }</pre>
*
* @author lingkang
* created by 2025/9/14 16:14
*/
public class Mysql7ScriptToH2 {
private final File tempOutH2Script = Paths.get(System.getProperty("user.dir"), "sql/h2.sql").toFile();
private final String mysqlScript;
private final String h2Url;
private final String h2Username;
private final String h2Password;
// mysql删除的语法
private static final List<String> removeMysqlGrammar = Arrays.asList(
"CHARACTER SET utf8mb4", "utf8mb4_general_ci", "utf8mb4_0900_ai_ci",
"COLLATE", "unsigned"
);
// 跳过的 startsWith 行
private static final List<String> jumpLineStartsWith = Arrays.asList(
"/*", "--", "LOCK", "UNLOCK",
"BEGIN;", "COMMIT;"
);
public Mysql7ScriptToH2(String mysqlScript, String h2Url, String h2Username, String h2Password) {
this.mysqlScript = mysqlScript;
this.h2Url = h2Url;
this.h2Username = h2Username;
this.h2Password = h2Password;
}
public void transform() {
Assert.isTrue(FileUtil.exist(this.mysqlScript), "mysql script not exist");
Assert.notBlank(this.h2Url, "h2 url not null");
convertMysqlScript();// 转换mysql脚本
BeeDataSource dataSource = new BeeDataSource(
Driver.class.getName(),
h2Url,
h2Username,
h2Password
);
MybatisFlexBootstrap flexBootstrap = MybatisFlexBootstrap.getInstance()
.setDataSource(dataSource)
// .setLogImpl(StdOutImpl.class) //日志输出配置
.start();
// 将 h2 脚本执行到h2数据库
ByteArrayOutputStream logWriter = new ByteArrayOutputStream();
ByteArrayOutputStream errWriter = new ByteArrayOutputStream();
PrintWriter logger = new PrintWriter(logWriter, true);
PrintWriter error = new PrintWriter(errWriter, true);
try (Connection conn = flexBootstrap.getDataSource().getConnection()) {
ScriptRunner runner = new ScriptRunner(conn);
// 设置是否输出日志
// runner.setLogWriter(null); // 不打印SQL日志
runner.setErrorLogWriter(error); // 不打印错误日志
runner.setLogWriter(logger);
// 读取SQL文件
File sqlFile = Paths.get(System.getProperty("user.dir"), "sql/h2.sql").toFile();
Reader reader = new BufferedReader(new FileReader(sqlFile));
// 执行脚本
runner.runScript(reader);
reader.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
log.info("execute sql: {}", logWriter.toString(StandardCharsets.UTF_8));
log.info("execute sql error: {}", errWriter.toString(StandardCharsets.UTF_8));
IoUtil.close(logWriter);
IoUtil.close(error);
}
// List<Row> rows = Db.selectAll("gen_templates");
// System.out.println(rows);
}
/**
* 转换mysql特有的类型、字符等
*/
private String convertMysqlLine(String line) {
for (String remove : removeMysqlGrammar) {
if (line.contains(remove)) {
line = line.replace(remove, "");
}
}
if (line.contains("bigint unsigned")) {
line = line.replace("bigint unsigned", "bigint");
}
if (line.contains("datetime")) {
line = line.replace("datetime", "TIMESTAMP");
}
if (line.contains("text")) {
line = line.replace("text", "LONGVARCHAR");
}
return line.trim();
}
private void convertMysqlScript() {
List<String> list = FileUtil.readUtf8Lines(mysqlScript);
StringBuilder sb = new StringBuilder();
StringBuilder insert = new StringBuilder();
StringBuilder index = new StringBuilder();
String tableName = "";
boolean startTable = false;
for (int i = 0; i < list.size(); i++) {
String str = list.get(i);
if (isJumpLine(str))
continue;
if (str.startsWith("INSERT INTO")) {
// 排除指定表数据插入
str = str.replace("\\'", "''");// 转义 \'
insert.append(str).append("\n\n");
continue;
}
if (str.startsWith("CREATE TABLE")) {
startTable = true;
// CREATE TABLE `sys_config` (
tableName = str.trim().split(" ")[2];
}
if (startTable && str.startsWith(") ENGINE")) {
// 结束处理表注释
if (str.contains("COMMENT=")) {
String comment = str.substring(str.indexOf("COMMENT=") + 8).trim();
str = ");";
str += "\n" + "COMMENT ON TABLE " + tableName + " IS " + comment + "\n";
}
// 判断前一行是否存在 分号
char topEnd = sb.charAt(sb.length() - 2);
if (topEnd == ',') {
// 删除上面的 分号
sb.delete(sb.length() - 2, sb.length()).append('\n');
}
startTable = false;
}
if (startTable) {
str = convertMysqlLine(str);
if (str.contains("PRIMARY KEY ")) {
// PRIMARY KEY (`user_id`,`role_id`) USING BTREE
String end = str.endsWith(",") ? "," : "";
if (str.contains("USING")) {
str = str.substring(0, str.indexOf("USING"));
} else if (",".equals(end)) {
str = str.substring(0, str.indexOf(","));
}
if (",".equals(end)) {// 说明下面的有索引
// KEY `sys_dict_data_label_IDX` (`label`) USING BTREE,
// KEY `sys_dict_data_value_IDX` (`dict_value`) USING BTREE
int j = 1;
String line = list.get(i + j);
while (line.contains("KEY ")) {
j++;
String[] split = line.split("`");
// CREATE INDEX email_send_smtp_id_IDX ON email_send (smtp_id);
index.append("CREATE INDEX ")
.append(split[1])
.append(" ON ")
.append(tableName)
.append(" (`")
.append(split[3])
.append("`);\n");
line = list.get(i + j);
}
}
}
if (str.startsWith("KEY ")) {
continue;
}
}
sb.append(str).append("\n");
}
sb.append("\n\n\n")
.append(index)
.append("\n\n\n")
.append(insert);
tempOutH2Script.delete();
FileUtil.writeUtf8String(sb.toString(), tempOutH2Script);
}
private boolean isJumpLine(String str) {
str = str.trim();
for (String jump : jumpLineStartsWith) {
if (str.startsWith(jump)) {
return true;
}
}
return false;
}
public static void main(String[] args) {
Mysql7ScriptToH2 mysql7ScriptToH2 = new Mysql7ScriptToH2(
Paths.get(System.getProperty("user.dir"), "sql/dump-mini-202509140144.sql").toFile().getAbsolutePath(),
"jdbc:h2:C:\\Users\\Administrator\\Desktop\\temp\\testH2",
null, null
);
mysql7ScriptToH2.transform();
}
}