使用java代码导出数据库sql文件
导出目标
导出包含数据库表结构、索引、外键以及表里面的数据
导出分析
1. 我们不需要导出所有的表 比如 _copy、_bak之类的备份表。
2. 我们不需要导出日志表（如 _log表）中的数据 只需要表结构
3. 基于SaaS架构的数据库一般会有一个租户id，比如项目id、租户id之类的，假设我们有需求，只想导出某个项目下的数据
导出工具类
private static final Logger logger = LoggerFactory.getLogger(MySQLDatabaseTableStructureAndDataSQLExporter.class);
/**
* 数据库sql采集
* @param ignoreTab 忽略采集哪些表
* @param logTab 哪些表只采集表结构、不采集insert语句
* @param projectIds 导出的项目id 多个逗号隔开【方法底层去判断数据库表是否有 project_id参数 如果有导出insert语句时就只导出指定项目下的数据】
*/
private static void collectSql(List<String> ignoreTab, List<String> logTab, String projectIds) {
String jdbcUrl = "jdbc:mysql://localhost:3306/数据库?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
String username = "你的用户名";
String password = "你的密码";
String databaseName = "数据库"; // 修改为你要导出的数据库名
String outputFilePath = databaseName+"output.sql"; // 修改为你想要输出的 SQL 文件路径
long begin = System.currentTimeMillis();
try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet tableResultSet = metaData.getTables(databaseName, null, null, new String[]{"TABLE"});
logger.info("数据库sql采集 开始 ");
try (FileWriter fileWriter = new FileWriter(outputFilePath)) {
fileWriter.write("SET FOREIGN_KEY_CHECKS = 0;" + "\n"); // 外键约束暂时不校验
while (tableResultSet.next()) {
String tableName = tableResultSet.getString("TABLE_NAME");
if (hasRegex(tableName, ignoreTab)) {
// 表名含有 ignoreTab 中的字符，则忽略
continue;
}
long w1 = System.currentTimeMillis();
logger.info("数据库sql采集 tableName={} ", tableName);
boolean isLoginTab = hasRegex(tableName, logTab); // 日志表里面的内容不同步
fileWriter.write("-- Table: " + tableName + "\n");
// 获取建表语句
StringBuilder createTableQuery = new StringBuilder();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SHOW CREATE TABLE " + tableName);
resultSet.next();
createTableQuery.append(resultSet.getString("Create Table")).append(";");
createTableQuery.append(System.lineSeparator());
fileWriter.write(createTableQuery.toString());
if (isLoginTab){
logger.info("数据库sql采集 tableName={}&耗时={}ms", tableName, (System.currentTimeMillis()-w1));
continue;
}
// 获取是否存在 "project_id" 字段
boolean hasProjectIdColumn = checkProjectIdColumn(connection, tableName);
String selectQuery ;
ResultSet dataResultSet;
PreparedStatement preparedStatement = null;
if (hasProjectIdColumn) {
selectQuery = "SELECT * FROM " + tableName + " WHERE project_id in (?)";
preparedStatement = connection.prepareStatement(selectQuery);
preparedStatement.setString(1, projectIds+""); // 修改为实际的 project_id 有些默认配置是项目id为0的
dataResultSet = preparedStatement.executeQuery();
} else{
selectQuery = "SELECT * FROM " + tableName;
dataResultSet = statement.executeQuery(selectQuery);
}
while (dataResultSet.next()) {
ResultSetMetaData rsMetaData = dataResultSet.getMetaData();
int columnCount = rsMetaData.getColumnCount();
StringBuilder insertQuery = new StringBuilder("INSERT INTO " + tableName + " VALUES (");
for (int i = 1; i <= columnCount; i++) {
Object value = dataResultSet.getObject(i);
if (i > 1) {
insertQuery.append(", ");
}
if (value == null) {
insertQuery.append("null");
}else {
if (rsMetaData.getColumnTypeName(i).equalsIgnoreCase("JSON")) {
// Handle JSON type data
String jsonValue = escapeJsonSpecialCharacters(value.toString());
insertQuery.append("'").append(jsonValue).append("'");
} else {
if (value instanceof Boolean) {
insertQuery.append("'").append((Boolean)value ? 1 : 0).append("'");
}else {
insertQuery.append("'").append(value).append("'");
}
}
}
}
insertQuery.append(");\n");
fileWriter.write(insertQuery.toString());
}
dataResultSet.close();
statement.close();
if (preparedStatement != null) {
preparedStatement.close();
}
logger.info("数据库sql采集 tableName={}&耗时={}ms", tableName, (System.currentTimeMillis()-w1));
}
fileWriter.write("SET FOREIGN_KEY_CHECKS = 1;");
} catch (IOException e) {
e.printStackTrace();
}
tableResultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
logger.info("数据库sql采集 结束 耗时={}ms ", (System.currentTimeMillis() - begin));
}
private static boolean hasRegex(String tableName, List<String> ignoreTab) {
if (CollectionUtils.isEmpty(ignoreTab)) {
return false;
}
for (String s : ignoreTab) {
if (tableName.contains(s)){
return true;
}
}
return false;
}
private static String escapeJsonSpecialCharacters(String jsonValue) {
return jsonValue
.replace("\n", "\\n")
.replace("\t", "\\t")
.replace("\r", "\\r")
.replace("\b", "\\b")
.replace("\f", "\\f")
.replace("\\", "\\\\")
.replace("'", "\\'");
}
private static boolean checkProjectIdColumn(Connection connection, String tableName) throws SQLException {
String query = "SHOW COLUMNS FROM " + tableName + " LIKE 'project_id'";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
ResultSet resultSet = preparedStatement.executeQuery();
return resultSet.next();
}
}