devbox
Gausst松鼠会
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySql表结构迁移到PostgreSql（pgsql） 方案_mysql迁移到postgre

作者：Gausst松鼠会 | 2024-04-10 22:23:17

赞

踩

mysql迁移到postgre

由于工作需要，要将原有的mysql的表结构和数据都迁移到pgsql里面，因为pgsql支持更大数据存储，还能做AI大模型的向量数据库。
网上找了很多种办法，这种跨数据库的迁移方案都是收费的。只有navicate 工具可以免费迁移，怎耐我们公司禁止使用navicate 。

没有办法，我只能首先java代码实现数据库表结构迁移工作。

下面就是代码，本打算从mysql读取所有的数据库结构直接插入到pgsql里面，但是实际却发现有很多不合理的地方，与改成了将所有的mysql结构生成pgsql 的结构打印出来。使用者可以拷贝打印出来的内容形成一个脚本再使用。

里面有类型转换我中用到我们实际使用到的结果，还有不少没列出来，使用时要注意一下。

  1. import java.sql.*;
  2. import java.util.*;
  3.  
  4. public class MysqlToPgsqlMigration {
  5.     private static final String MYSQL_URL = "jdbc:mysql://127.0.0.1:3306/test";
  6.     private static final String MYSQL_USER = "root";
  7.     private static final String MYSQL_PASSWORD = "root";
  8.  
  9.     private static final String POSTGRESQL_URL = "jdbc:postgresql://127.0.0.1:5432/jeecg-boot";
  10.     private static final String POSTGRESQL_USER = "root";
  11.     private static final String POSTGRESQL_PASSWORD = "root1";
  12.  
  13.     public static void main(String[] args) throws SQLException, ClassNotFoundException {
  14.         Class.forName("com.mysql.cj.jdbc.Driver");
  15.         Class.forName("org.postgresql.Driver");
  16.  
  17.         try (Connection mysqlConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
  18.              Connection pgsqlConn = DriverManager.getConnection(POSTGRESQL_URL, POSTGRESQL_USER, POSTGRESQL_PASSWORD)) {
  19.  
  20.             DatabaseMetaData mysqlMeta = mysqlConn.getMetaData();
  21.             Statement pgsqlStmt = pgsqlConn.createStatement();
  22.  
  23.             ResultSet tablesRs = mysqlMeta.getTables(null, null, "%", new String[]{"TABLE"});
  24.  
  25.             while (tablesRs.next()) {
  26.                 String tableName = tablesRs.getString("TABLE_NAME");
  27.                 // 重建表结构
  28.                 StringBuilder createTableSql = new StringBuilder("CREATE TABLE ");
  29.                 createTableSql.append(tableName).append(" (\n");
  30.  
  31.                 ResultSet columnsRs = mysqlMeta.getColumns(null, null, tableName, null);
  32.                 List<String> primaryKeyColumns = new ArrayList<>();
  33.                 List<String> indexKeyColumns = new ArrayList<>();
  34.                 List<String> columnComments = new ArrayList<>();
  35.                 List<String>  uniqueindexKeyColumns = new ArrayList<>();
  36.                 while (columnsRs.next()) {
  37.                     String columnName = columnsRs.getString("COLUMN_NAME");
  38.                     int dataType = columnsRs.getInt("DATA_TYPE");
  39.                     String typeName = mapColumnType(dataType);
  40.                     int size = columnsRs.getInt("COLUMN_SIZE");
  41.                     boolean isNullable = columnsRs.getBoolean("NULLABLE");
  42.                     String columnDef = columnsRs.getString("COLUMN_DEF");
  43.                     String columnComment = columnsRs.getString("REMARKS");
  44.  
  45.                     createTableSql.append("\t").append(columnName).append(' ').append(typeName);
  46.                     if (!"INTEGER".equalsIgnoreCase(typeName)
  47.                             &&!"int4".equalsIgnoreCase(typeName)
  48.                             &&!"int8".equalsIgnoreCase(typeName)
  49.                             &&!"smallint".equalsIgnoreCase(typeName)
  50.                             &&!"bigint".equalsIgnoreCase(typeName)
  51.                             &&!"text".equalsIgnoreCase(typeName)){
  52.                         if (size > 0) {
  53.                             createTableSql.append('(').append(size).append(')');
  54.                         }
  55.                     }
  56.  
  57.                     if (!isNullable) {
  58.                         createTableSql.append(" NOT NULL");
  59.                     }
  60.  
  61.                     if (columnDef != null) {
  62.                         if ("INTEGER".equalsIgnoreCase(typeName)
  63.                                 ||"int4".equalsIgnoreCase(typeName)
  64.                                 ||"int8".equalsIgnoreCase(typeName)
  65.                                 ||"smallint".equalsIgnoreCase(typeName)
  66.                                 ||"bigint".equalsIgnoreCase(typeName)
  67.                                 ||"NUMERIC".equalsIgnoreCase(typeName)){
  68.                             createTableSql.append(" DEFAULT ").append(columnDef);
  69.                         }else if("TIMESTAMP".equalsIgnoreCase(typeName)){
  70.                             createTableSql.append(" DEFAULT ").append("TO_TIMESTAMP('2024-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')");
  71.                         }else{
  72.                             createTableSql.append(" DEFAULT ").append("'" + escapeSingleQuotes(columnDef) + "'");
  73.                         }
  74.                     }
  75.  
  76.                     columnComments.add(addColumnComment(tableName, columnName, columnComment, pgsqlStmt));
  77.  
  78.                     createTableSql.append(",\n");
  79.  
  80.                     // 检查是否为主键
  81.                     ResultSet pkRs = mysqlMeta.getPrimaryKeys(null, null, tableName);
  82.                     while (pkRs.next() && pkRs.getString("COLUMN_NAME").equals(columnName)) {
  83.                         primaryKeyColumns.add(columnName);
  84.                     }
  85.                     pkRs.close();
  86.                     
  87.                 }
  88.  
  89.                 // 移除最后一个逗号和换行符
  90.                 createTableSql.setLength(createTableSql.length() - 2);
  91.  
  92.                 // 添加主键约束
  93.                 if (!primaryKeyColumns.isEmpty()) {
  94.                     createTableSql.append(", \n    PRIMARY KEY (")
  95.                             .append(String.join(",", primaryKeyColumns))
  96.                             .append(")");
  97.                 }
  98.                 
  99.  
  100.                 createTableSql.append("\n);");
  101.                 System.out.println(createTableSql);
  102.                 //pgsqlStmt.executeUpdate(createTableSql.toString());
  103.  
  104.                 // 处理索引及联合索引
  105.                 Map<String,List<String>> unIndexMap = new HashMap<>();
  106.                 ResultSet uniqueindexSet = mysqlMeta.getIndexInfo(null, null, tableName, true, false);
  107.                 while (uniqueindexSet.next()) {
  108.                     String indexName = uniqueindexSet.getString("INDEX_NAME"); // 索引名称
  109.                     String columnName = uniqueindexSet.getString("COLUMN_NAME"); // 索引列名
  110.                     /*boolean isNonUnique = uniqueindexSet.getBoolean("NON_UNIQUE"); // 是否非唯一索引（0表示唯一索引，1表示非唯一索引）
  111.                     System.out.println("indexName="+indexName+",columnName="+columnName+",isNonUnique="+isNonUnique);*/
  112.                     if ("PRIMARY".equalsIgnoreCase(indexName)){
  113.                         continue;
  114.                     }
  115.                     List<String> indexCols = unIndexMap.get(indexName);
  116.                     if (indexCols == null){
  117.                         indexCols = new ArrayList<>();
  118.                     }
  119.                     if (!indexCols.contains(columnName)){
  120.                         indexCols.add(columnName);
  121.                     }
  122.                     unIndexMap.put(indexName,indexCols);
  123.                     
  124.                     uniqueindexKeyColumns.add(columnName);
  125.                 }
  126.                 for (Map.Entry<String, List<String>> entry : unIndexMap.entrySet()) {
  127.                     String indexName = entry.getKey();
  128.                     List<String> list = entry.getValue();
  129.                     if (list.size()>1){ // 联合索引
  130.                         System.out.println("CREATE UNIQUE INDEX "+indexName+" ON "+tableName+"("+String.join(",",list)+");");
  131.                     }else{// 普通索引
  132.                         if (indexName.startsWith("uni")||indexName.startsWith("uk_")){
  133.                             System.out.println("CREATE UNIQUE INDEX "+indexName+" ON "+tableName+"("+list.get(0)+");");
  134.                         }else{
  135.                             System.out.println("CREATE INDEX "+indexName+" ON "+tableName+"("+list.get(0)+");");
  136.                         }
  137.                     }
  138.                 }
  139.                 uniqueindexSet.close();
  140.  
  141.                 // 处理外键索引
  142.                 /*ResultSet foreignKeysRS = mysqlMeta.getImportedKeys(null, null, tableName);
  143.                 while (foreignKeysRS.next()) {
  144.                     String fkName = foreignKeysRS.getString("FK_NAME"); // 外键约束名称
  145.                     String fkColumnName = foreignKeysRS.getString("FKCOLUMN_NAME"); // 本表中对应的外键列名
  146.                     String pkTableName = foreignKeysRS.getString("PKTABLE_NAME"); // 主键所在的表名
  147.                     String pkColumnName = foreignKeysRS.getString("PKCOLUMN_NAME"); // 主键列名
  149.                     System.out.println("Foreign Key: " + fkName);
  150.                     System.out.println("From Column: " + fkColumnName + " in Table " + tableName);
  151.                     System.out.println("References Column: " + pkColumnName + " in Table " + pkTableName);
  152.                 }
  153.                 foreignKeysRS.close();*/
  154.                 
  155.                 //字段注释
  156.                 if (!columnComments.isEmpty()){
  157.                     for (String comment : columnComments) {
  158.                         System.out.println(comment);
  159.                     }
  160.                 }
  161.                 // 获取表注释并添加
  162.                 String tableComment = getTableComment(tableName, mysqlMeta);
  163.                 System.out.println(addTableComment(tableName, tableComment, pgsqlStmt));
  164.                 System.out.println("\n");
  165.                 // 插入数据
  166.                 //copyTableData(tableName, mysqlConn, pgsqlConn);
  167.             }
  168.  
  169.             tablesRs.close();
  170.         }
  171.     }
  172.  
  173.     private static String addTableComment(String tableName, String comment, Statement stmt) throws SQLException {
  174.         /*if (comment != null && !comment.isEmpty()) {
  175.             stmt.executeUpdate("COMMENT ON TABLE " + tableName + " IS '" + escapeSingleQuotes(comment) + "'");
  176.         }*/
  177.         return "COMMENT ON TABLE " + tableName + " IS '" + escapeSingleQuotes(comment) + "';";
  178.     }
  179.  
  180.     private static String addColumnComment(String tableName, String columnName, String comment, Statement stmt) throws SQLException {
  181.         return "COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + escapeSingleQuotes(comment) + "';";
  182.         /*if (comment != null && !comment.isEmpty()) {
  183.             stmt.executeUpdate("COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + escapeSingleQuotes(comment) + "'");
  184.         }*/
  185.     }
  186.  
  187.     private static String getTableComment(String tableName, DatabaseMetaData meta) throws SQLException {
  188.         ResultSet rs = meta.getTables(null, null, tableName, null);
  189.         if (rs.next()) {
  190.             return rs.getString("REMARKS");
  191.         }
  192.         return "";
  193.     }
  194.  
  195.     private static String mapColumnType(int mysqlDataType) {
  196.         // 这里仅提供一种简单的映射方式，实际情况可能更复杂
  197.         switch (mysqlDataType) {
  198. //            case Types.BIT:
  199. //                return "BOOLEAN";
  200.             case Types.TINYINT:
  201.                 return "SMALLINT";
  202.             case Types.BIGINT:
  203.                 return "BIGINT";
  204.             case Types.INTEGER:
  205.                 return "INTEGER";
  206.             case Types.DATE:
  207.                 return "timestamp";
  208.             case Types.DOUBLE:
  209.                 return "NUMERIC";
  210.             case Types.NUMERIC:
  211.                 return "NUMERIC";
  212.             case Types.DECIMAL:
  213.                 return "NUMERIC";
  214.             case Types.LONGVARCHAR:
  215.                 return "text";
  216.             case Types.TIMESTAMP:
  217.                 return "TIMESTAMP";
  218.             case Types.BLOB:
  219.                 return "BYTEA";
  220.             // 其他类型...
  221.             default:
  222.                 return "varchar";
  223.         }
  224.     }
  225.  
  226.     private static void copyTableData(String tableName, Connection mysqlConn, Connection pgsqlConn) throws SQLException {
  227.         String selectAllDataSql = "SELECT * FROM " + tableName;
  228.         PreparedStatement mysqlPs = mysqlConn.prepareStatement(selectAllDataSql);
  229.         ResultSet dataRs = mysqlPs.executeQuery();
  230.         ResultSetMetaData metaData = dataRs.getMetaData();
  231.         StringBuilder insertSql = new StringBuilder("INSERT INTO ").append(tableName).append(" VALUES (");
  232.  
  233.         for (int i = 1; i <= metaData.getColumnCount(); i++) {
  234.             insertSql.append("?");
  235.             if (i < metaData.getColumnCount()) {
  236.                 insertSql.append(",");
  237.             }
  238.         }
  239.         insertSql.append(")");
  240.  
  241.         PreparedStatement pgsqlPs = pgsqlConn.prepareStatement(insertSql.toString());
  242.         while (dataRs.next()) {
  243.             for (int i = 1; i <= metaData.getColumnCount(); i++) {
  244.                 pgsqlPs.setObject(i, dataRs.getObject(i));
  245.             }
  246.             pgsqlPs.addBatch();
  247.         }
  248.         pgsqlPs.executeBatch();
  249.     }
  250.  
  251.     private static String escapeSingleQuotes(String str) {
  252.         return str.replace("'", "''");
  253.     }
  254. }

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/401332
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号