一、合并多条Insert语句
提高批量插入语句执行效率的办法之一就是将多条INSERT合并为一条，以下代码可以自动合并INSERT语句，自动忽略非INSERT语句，可以设置是否过滤注释。
package org.example;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IORuntimeException;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.text.StrBuilder;
import java.io.*;
import java.nio.charset.StandardCharsets;
/**
* 主要功能：将多个insert语句合并成一个
* 参考了这个代码 cn.hutool.core.text.csv.CsvParser
*/
public class Main {
public static void main(String[] args) {
// x个insert语句合并为一个
final int x = 50;
// 移除注释
final boolean rmComments = true;
try (// 输入文件是可以是包含非Insert语句的混合文件，例如由Navicat、Dbeaver等软件的转储的SQL文件（包含结构和数据）
BufferedReader reader = FileUtil.getReader("D://mysqlintellij.sql", StandardCharsets.UTF_8);
BufferedWriter writer = FileUtil.getWriter("D://mysqlintellij_.sql", StandardCharsets.UTF_8, false)) {
int n = 0;
SqlParser sqlParser = new SqlParser(reader, rmComments);
// 上一个select语句的关键部分
String preInsertStatementKey = null;
String preSql = null;
boolean preSqlIsInsert = false;
while (sqlParser.hasNext()) {
SqlParser.SQLPart sqlPart = sqlParser.readOneSql();
String sql = sqlPart.getSql();
// System.out.println(sqlPart.getSqlNo());
// 判断当前sql是不是insert语句
boolean isInsertSql = sql != null && !sql.isEmpty() && sql.toLowerCase().contains("insert");
if (isInsertSql) {
String insertStatementKey = sql.substring(sql.toLowerCase().indexOf("insert"), sql.toLowerCase().indexOf("values") + 6);
if (preSqlIsInsert) {
boolean sameInsert = insertStatementKey.equals(preInsertStatementKey);
if (n < x - 1 && sameInsert) {
preSql = preSql.replace(";", ",");
}
if (n > 0) {
preSql = preSql.substring(preSql.indexOf("VALUES") + 6);
}
writer.append(preSql);
if (n == x - 1 || !sameInsert) {
n = 0;
writer.append("\n");
} else {
n++;
}
// System.out.println(n);
} else {
writer.append(preSql).append("\n");
}
preSqlIsInsert = true;
preInsertStatementKey = insertStatementKey;
} else {
if (preSqlIsInsert) {
if (n > 0) {
preSql = preSql.substring(preSql.indexOf("VALUES") + 6);
}
writer.append(preSql).append("\n");
n = 0;
} else if (preSql != null) {
writer.append(preSql).append("\n");
}
}
preSql = sql;
}
System.out.println("完成");
} catch (Exception e) {
e.printStackTrace();
}
}
static class SqlParser {
private final Reader reader;
/**
* 当前sql编号
*/
private long sqlNo = -1;
/**
* 前一个特殊分界字符
*/
private int preChar = -1;
private int prePreChar = -1;
private final Buffer buf = new Buffer(IoUtil.DEFAULT_LARGE_BUFFER_SIZE);
private final boolean rmComments;
/**
* 在多行注释中
*/
boolean inMComment = false;
/**
* 在单行注释中
*/
boolean inLComment = false;
private boolean finished;
/**
* 当前读取SQL
*/
private final StrBuilder currentField = new StrBuilder(512);
SqlParser(Reader reader, boolean rmComments) {
this.reader = reader;
this.rmComments = rmComments;
}
public boolean hasNext() {
return !finished;
}
static class SQLPart {
private long sqlNo;
private String sql;
public SQLPart(long sqlNo, String sql) {
this.sqlNo = sqlNo;
this.sql = sql;
}
public long getSqlNo() {
return sqlNo;
}
public void setSqlNo(long sqlNo) {
this.sqlNo = sqlNo;
}
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
}
public SQLPart readOneSql() {
final Buffer buf = this.buf;
int copyLen = 0; // 拷贝长度
final StrBuilder currentField = this.currentField;
while (true) {
if (!buf.hasRemaining()) {
// 此Buffer读取结束，开始读取下一段
if (copyLen > 0) {
buf.appendTo(currentField, copyLen);
// 此处无需mark，read方法会重置mark
}
if (buf.read(this.reader) < 0) {
// CSV读取结束
finished = true;
if (currentField.hasContent() || preChar == ';') {
// 剩余部分作为一个SQL返回
return new SQLPart(++sqlNo, currentField.toStringAndReset());
}
break;
}
// 重置
copyLen = 0;
}
final char c = buf.get();
if (rmComments) {
// 多行注释
if (c == '*' && preChar == '/') {
copyLen = 0;
inMComment = true;
}
if (inMComment) {
if (c == '/' && preChar == '*') {
inMComment = false;
}
buf.mark();
prePreChar = preChar;
preChar = c;
continue;
}
// 单行注释
if (c == ' ' && preChar == '-' && prePreChar == '-') {
copyLen = 0;
inLComment = true;
}
if (inLComment) {
if (c == '\n' || c == '\r') {
inLComment = false;
}
buf.mark();
prePreChar = preChar;
preChar = c;
continue;
}
}
if (c == ';') {
if (copyLen > 0) {
buf.appendTo(currentField, copyLen);
}
buf.mark();
prePreChar = preChar;
preChar = c;
return new SQLPart(++sqlNo, currentField.append(c).toStringAndReset());
} else if (this.preChar == ';' && (c == '\r' || c == '\n' || c == ' ')) {
buf.mark();
} else {
copyLen++;
prePreChar = preChar;
preChar = c;
}
}
sqlNo++;
return null;
}
}
private static class Buffer implements Serializable {
private static final long serialVersionUID = 1L;
final char[] buf;
/**
* 标记位置，用于读数据
*/
private int mark;
/**
* 当前位置
*/
private int position;
/**
* 读取的数据长度，一般小于buf.length，-1表示无数据
*/
private int limit;
Buffer(int capacity) {
buf = new char[capacity];
}
/**
* 是否还有未读数据
*
* @return 是否还有未读数据
*/
public final boolean hasRemaining() {
return position < limit;
}
/**
* 读取到缓存<br>
* 全量读取，会重置Buffer中所有数据
*
* @param reader {@link Reader}
*/
int read(Reader reader) {
int length;
try {
length = reader.read(this.buf);
} catch (IOException e) {
throw new IORuntimeException(e);
}
this.mark = 0;
this.position = 0;
this.limit = length;
return length;
}
/**
* 先获取当前字符，再将当前位置后移一位<br>
* 此方法不检查是否到了数组末尾，请自行使用{@link #hasRemaining()}判断。
*
* @return 当前位置字符
* @see #hasRemaining()
*/
char get() {
return this.buf[this.position++];
}
/**
* 标记位置记为下次读取位置
*/
void mark() {
this.mark = this.position;
}
/**
* 将数据追加到{@link StrBuilder}，追加结束后需手动调用{@link #mark()} 重置读取位置
*
* @param builder {@link StrBuilder}
* @param length 追加的长度
* @see #mark()
*/
void appendTo(StrBuilder builder, int length) {
builder.append(this.buf, this.mark, length);
}
}
}