原mysql 表查询存在索引，
查询sql含force index(字段)，SQLite不支持，会报错
解决办法，要么删除索引，要么强制执行索引 换成 INDEXED BY,
String select sql="select * ";
String fromSql=" from tableName force index (START_TIME_TYPE) where 1=1
if (fromSql.contains("force index")){
fromSql= fromSql.replaceAll("(?i)\\bFROM\\s+(\\w+)(\\s+\\w+)?\\s+force\\s+index\\s*\\(\\s*(\\w+)\\s*\\)",
"FROM $1 $2 INDEXED BY $3");
}
这里就需要给sqlite数据库表添加对应索引，不然会报错SQLiteException: no such index: START_TIME_TYPE
生产数据库中，表多，索引比较多，在原先迁移表工具添加索引迁移，暂时未发现问题，需多多测试
package changeDataBase;
import java.sql.*;
import java.util.*;
public class MySQLToSQLiteMigration {
private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/database";
private static final String MYSQL_USER = "admin";
private static final String MYSQL_PASSWORD = "123456";
private static final String SQLITE_URL = "jdbc:sqlite:D:/database/database.sqlite";
public static void main(String[] args) {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException var2) {
System.err.println("SQLite JDBC driver not found!");
var2.printStackTrace();
return;
}
List<String> tableNames = getTableNamesFromMySQL();
migrateTablesToSQLite(tableNames);
}
private static List<String> getTableNamesFromMySQL() {
ArrayList<String> tableNames = new ArrayList<>();
try (Connection conn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD)) {
DatabaseMetaData metaData = conn.getMetaData();
ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
while (rs.next()) {
tableNames.add(rs.getString("TABLE_NAME"));
}
} catch (SQLException var6) {
var6.printStackTrace();
}
return tableNames;
}
private static void migrateTablesToSQLite(List<String> tableNames) {
try (Connection mysqlConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
Connection sqliteConn = DriverManager.getConnection(SQLITE_URL)) {
Iterator<String> var3 = tableNames.iterator();
while (var3.hasNext()) {
String tableName = var3.next();
System.out.println("Migrating table: " + tableName);
migrateTable(mysqlConn, sqliteConn, tableName);
}
} catch (SQLException var9) {
var9.printStackTrace();
}
}
private static void migrateTable(Connection mysqlConn, Connection sqliteConn, String tableName) throws SQLException {
String selectSql = "SELECT * FROM " + tableName;
try (Statement stmt = mysqlConn.createStatement();
ResultSet rs = stmt.executeQuery(selectSql)) {
String createTableSql = getCreateTableSql(mysqlConn, tableName);
try (Statement sqliteStmt = sqliteConn.createStatement()) {
sqliteStmt.execute(createTableSql);
}
String insertSql = getInsertSql(rs.getMetaData(), tableName);
try (PreparedStatement sqlitePstmt = sqliteConn.prepareStatement(insertSql)) {
while (rs.next()) {
for (int i = 1; i <= rs.getMetaData().getColumnCount(); ++i) {
String columnName = rs.getMetaData().getColumnName(i);
String columnType = rs.getMetaData().getColumnTypeName(i);
// 特殊处理时间字段
if ("DATETIME".equalsIgnoreCase(columnType) || "TIMESTAMP".equalsIgnoreCase(columnType)) {
Object value = rs.getObject(i);
if (value instanceof java.sql.Timestamp) {
// 将 Timestamp 转换为标准日期时间格式
java.sql.Timestamp timestamp = (java.sql.Timestamp) value;
sqlitePstmt.setString(i, timestamp.toString());
} else {
sqlitePstmt.setObject(i, value);
}
} else {
sqlitePstmt.setObject(i, rs.getObject(i));
}
}
sqlitePstmt.addBatch();
}
sqlitePstmt.executeBatch();
}
}
//处理索引
migrateIndexes(mysqlConn, sqliteConn, tableName);
}
private static String getCreateTableSql(Connection mysqlConn, String tableName) throws SQLException {
DatabaseMetaData metaData = mysqlConn.getMetaData();
ResultSet rs = metaData.getColumns(null, null, tableName, "%");
StringBuilder sb = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
sb.append(tableName).append(" (");
List<String> columns = new ArrayList<>();
String primaryKey = null;
while (rs.next()) {
String columnName = rs.getString("COLUMN_NAME");
String columnType = getSQLiteType(rs.getString("TYPE_NAME"));
columns.add(columnName + " " + columnType);
}
// 获取主键信息
String primaryKeySql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND CONSTRAINT_NAME = 'PRIMARY'";
try (PreparedStatement pstmt = mysqlConn.prepareStatement(primaryKeySql)) {
pstmt.setString(1, "database");
pstmt.setString(2, tableName);
try (ResultSet pkRs = pstmt.executeQuery()) {
if (pkRs.next()) {
primaryKey = pkRs.getString("COLUMN_NAME");
}
}
}
sb.append(String.join(", ", columns));
if (primaryKey != null) {
sb.append(", PRIMARY KEY (").append(primaryKey).append(")");
}
sb.append(");");
return sb.toString();
}
private static String getInsertSql(ResultSetMetaData metaData, String tableName) throws SQLException {
StringBuilder sb = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
for (int i = 1; i <= metaData.getColumnCount(); ++i) {
sb.append(metaData.getColumnName(i));
if (i < metaData.getColumnCount()) {
sb.append(", ");
}
}
sb.append(") VALUES (");
for (int i = 1; i <= metaData.getColumnCount(); ++i) {
sb.append("?");
if (i < metaData.getColumnCount()) {
sb.append(", ");
}
}
sb.append(");");
return sb.toString();
}
private static String getSQLiteType(String mysqlType) {
switch (mysqlType.toUpperCase()) {
case "INT":
case "INTEGER":
return "INTEGER";
case "VARCHAR":
case "CHAR":
return "TEXT";
case "DATE":
return "DATE";
case "DATETIME":
case "TIMESTAMP":
return "DATETIME";
case "DECIMAL":
return "NUMERIC";
case "FLOAT":
case "DOUBLE":
return "REAL";
default:
return "TEXT";
}
}
//添加索引处理
/**
* 把 MySQL 表中除主键外的所有索引迁移到 SQLite
*/
private static void migrateIndexes(Connection mysqlConn,
Connection sqliteConn,
String tableName) throws SQLException {
// 1. 先查索引列
String sql =
"SELECT INDEX_NAME, NON_UNIQUE, COLUMN_NAME, SEQ_IN_INDEX " +
"FROM INFORMATION_SCHEMA.STATISTICS " +
"WHERE TABLE_SCHEMA = DATABASE() " +
" AND TABLE_NAME = ? " +
" AND INDEX_NAME != 'PRIMARY' " +
"ORDER BY INDEX_NAME, SEQ_IN_INDEX";
// Map<索引名, 列列表>
Map<String, List<String>> indexMap = new LinkedHashMap<>();
try (PreparedStatement ps = mysqlConn.prepareStatement(sql)) {
ps.setString(1, tableName);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String idxName = rs.getString("INDEX_NAME");
indexMap.computeIfAbsent(idxName, k -> new ArrayList<>())
.add("\"" + rs.getString("COLUMN_NAME") + "\"");
}
}
}
// 2. 逐个在 SQLite 建索引
for (Map.Entry<String, List<String>> e : indexMap.entrySet()) {
String cols = String.join(", ", e.getValue());
// 索引名在 SQLite 中保持同名，避免冲突可加前缀
String createIdx = String.format(
"CREATE %s INDEX IF NOT EXISTS \"%s\" ON \"%s\" (%s);",
e.getKey().toUpperCase().startsWith("UNIQUE") ? "UNIQUE" : "",
e.getKey(), tableName, cols);
try (Statement st = sqliteConn.createStatement()) {
st.execute(createIdx);
System.out.println(" " + createIdx.trim());
}
}
}
}