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

已于 2024-02-04 11:30:09 修改
阅读量937 收藏
点赞数 3
文章标签： mysql postgresql 数据库
于 2024-02-04 11:29:31 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/swangooth/article/details/136022803
版权
本文介绍了一位开发者如何使用Java代码手动迁移MySQL数据库表结构到PostgreSQL，包括获取表结构、类型转换和生成迁移脚本，由于商业限制未使用付费工具而选择自定义解决方案。
摘要由CSDN通过智能技术生成

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

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

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

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

import java.sql.*;
import java.util.*;

public class MysqlToPgsqlMigration {
    private static final String MYSQL_URL = "jdbc:mysql://127.0.0.1:3306/test";
    private static final String MYSQL_USER = "root";
    private static final String MYSQL_PASSWORD = "root";

    private static final String POSTGRESQL_URL = "jdbc:postgresql://127.0.0.1:5432/jeecg-boot";
    private static final String POSTGRESQL_USER = "root";
    private static final String POSTGRESQL_PASSWORD = "root1";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Class.forName("org.postgresql.Driver");

        try (Connection mysqlConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
             Connection pgsqlConn = DriverManager.getConnection(POSTGRESQL_URL, POSTGRESQL_USER, POSTGRESQL_PASSWORD)) {

            DatabaseMetaData mysqlMeta = mysqlConn.getMetaData();
            Statement pgsqlStmt = pgsqlConn.createStatement();

            ResultSet tablesRs = mysqlMeta.getTables(null, null, "%", new String[]{"TABLE"});

            while (tablesRs.next()) {
                String tableName = tablesRs.getString("TABLE_NAME");
                // 重建表结构
                StringBuilder createTableSql = new StringBuilder("CREATE TABLE ");
                createTableSql.append(tableName).append(" (\n");

                ResultSet columnsRs = mysqlMeta.getColumns(null, null, tableName, null);
                List<String> primaryKeyColumns = new ArrayList<>();
                List<String> indexKeyColumns = new ArrayList<>();
                List<String> columnComments = new ArrayList<>();
                List<String>  uniqueindexKeyColumns = new ArrayList<>();
                while (columnsRs.next()) {
                    String columnName = columnsRs.getString("COLUMN_NAME");
                    int dataType = columnsRs.getInt("DATA_TYPE");
                    String typeName = mapColumnType(dataType);
                    int size = columnsRs.getInt("COLUMN_SIZE");
                    boolean isNullable = columnsRs.getBoolean("NULLABLE");
                    String columnDef = columnsRs.getString("COLUMN_DEF");
                    String columnComment = columnsRs.getString("REMARKS");

                    createTableSql.append("\t").append(columnName).append(' ').append(typeName);
                    if (!"INTEGER".equalsIgnoreCase(typeName)
                            &&!"int4".equalsIgnoreCase(typeName)
                            &&!"int8".equalsIgnoreCase(typeName)
                            &&!"smallint".equalsIgnoreCase(typeName)
                            &&!"bigint".equalsIgnoreCase(typeName)
                            &&!"text".equalsIgnoreCase(typeName)){
                        if (size > 0) {
                            createTableSql.append('(').append(size).append(')');
                        }
                    }

                    if (!isNullable) {
                        createTableSql.append(" NOT NULL");
                    }

                    if (columnDef != null) {
                        if ("INTEGER".equalsIgnoreCase(typeName)
                                ||"int4".equalsIgnoreCase(typeName)
                                ||"int8".equalsIgnoreCase(typeName)
                                ||"smallint".equalsIgnoreCase(typeName)
                                ||"bigint".equalsIgnoreCase(typeName)
                                ||"NUMERIC".equalsIgnoreCase(typeName)){
                            createTableSql.append(" DEFAULT ").append(columnDef);
                        }else if("TIMESTAMP".equalsIgnoreCase(typeName)){
                            createTableSql.append(" DEFAULT ").append("TO_TIMESTAMP('2024-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')");
                        }else{
                            createTableSql.append(" DEFAULT ").append("'" + escapeSingleQuotes(columnDef) + "'");
                        }
                    }

                    columnComments.add(addColumnComment(tableName, columnName, columnComment, pgsqlStmt));

                    createTableSql.append(",\n");

                    // 检查是否为主键
                    ResultSet pkRs = mysqlMeta.getPrimaryKeys(null, null, tableName);
                    while (pkRs.next() && pkRs.getString("COLUMN_NAME").equals(columnName)) {
                        primaryKeyColumns.add(columnName);
                    }
                    pkRs.close();
                    
                }

                // 移除最后一个逗号和换行符
                createTableSql.setLength(createTableSql.length() - 2);

                // 添加主键约束
                if (!primaryKeyColumns.isEmpty()) {
                    createTableSql.append(", \n    PRIMARY KEY (")
                            .append(String.join(",", primaryKeyColumns))
                            .append(")");
                }
                

                createTableSql.append("\n);");
                System.out.println(createTableSql);
                //pgsqlStmt.executeUpdate(createTableSql.toString());

                // 处理索引及联合索引
                Map<String,List<String>> unIndexMap = new HashMap<>();
                ResultSet uniqueindexSet = mysqlMeta.getIndexInfo(null, null, tableName, true, false);
                while (uniqueindexSet.next()) {
                    String indexName = uniqueindexSet.getString("INDEX_NAME"); // 索引名称
                    String columnName = uniqueindexSet.getString("COLUMN_NAME"); // 索引列名
                    /*boolean isNonUnique = uniqueindexSet.getBoolean("NON_UNIQUE"); // 是否非唯一索引（0表示唯一索引，1表示非唯一索引）
                    System.out.println("indexName="+indexName+",columnName="+columnName+",isNonUnique="+isNonUnique);*/
                    if ("PRIMARY".equalsIgnoreCase(indexName)){
                        continue;
                    }
                    List<String> indexCols = unIndexMap.get(indexName);
                    if (indexCols == null){
                        indexCols = new ArrayList<>();
                    }
                    if (!indexCols.contains(columnName)){
                        indexCols.add(columnName);
                    }
                    unIndexMap.put(indexName,indexCols);
                    
                    uniqueindexKeyColumns.add(columnName);
                }
                for (Map.Entry<String, List<String>> entry : unIndexMap.entrySet()) {
                    String indexName = entry.getKey();
                    List<String> list = entry.getValue();
                    if (list.size()>1){ // 联合索引
                        System.out.println("CREATE UNIQUE INDEX "+indexName+" ON "+tableName+"("+String.join(",",list)+");");
                    }else{// 普通索引
                        if (indexName.startsWith("uni")||indexName.startsWith("uk_")){
                            System.out.println("CREATE UNIQUE INDEX "+indexName+" ON "+tableName+"("+list.get(0)+");");
                        }else{
                            System.out.println("CREATE INDEX "+indexName+" ON "+tableName+"("+list.get(0)+");");
                        }
                    }
                }
                uniqueindexSet.close();

                // 处理外键索引
                /*ResultSet foreignKeysRS = mysqlMeta.getImportedKeys(null, null, tableName);
                while (foreignKeysRS.next()) {
                    String fkName = foreignKeysRS.getString("FK_NAME"); // 外键约束名称
                    String fkColumnName = foreignKeysRS.getString("FKCOLUMN_NAME"); // 本表中对应的外键列名
                    String pkTableName = foreignKeysRS.getString("PKTABLE_NAME"); // 主键所在的表名
                    String pkColumnName = foreignKeysRS.getString("PKCOLUMN_NAME"); // 主键列名

                    System.out.println("Foreign Key: " + fkName);
                    System.out.println("From Column: " + fkColumnName + " in Table " + tableName);
                    System.out.println("References Column: " + pkColumnName + " in Table " + pkTableName);
                }
                foreignKeysRS.close();*/
                
                //字段注释
                if (!columnComments.isEmpty()){
                    for (String comment : columnComments) {
                        System.out.println(comment);
                    }
                }
                // 获取表注释并添加
                String tableComment = getTableComment(tableName, mysqlMeta);
                System.out.println(addTableComment(tableName, tableComment, pgsqlStmt));
                System.out.println("\n");
                // 插入数据
                //copyTableData(tableName, mysqlConn, pgsqlConn);
            }

            tablesRs.close();
        }
    }

    private static String addTableComment(String tableName, String comment, Statement stmt) throws SQLException {
        /*if (comment != null && !comment.isEmpty()) {
            stmt.executeUpdate("COMMENT ON TABLE " + tableName + " IS '" + escapeSingleQuotes(comment) + "'");
        }*/
        return "COMMENT ON TABLE " + tableName + " IS '" + escapeSingleQuotes(comment) + "';";
    }

    private static String addColumnComment(String tableName, String columnName, String comment, Statement stmt) throws SQLException {
        return "COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + escapeSingleQuotes(comment) + "';";
        /*if (comment != null && !comment.isEmpty()) {
            stmt.executeUpdate("COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + escapeSingleQuotes(comment) + "'");
        }*/
    }

    private static String getTableComment(String tableName, DatabaseMetaData meta) throws SQLException {
        ResultSet rs = meta.getTables(null, null, tableName, null);
        if (rs.next()) {
            return rs.getString("REMARKS");
        }
        return "";
    }

    private static String mapColumnType(int mysqlDataType) {
        // 这里仅提供一种简单的映射方式，实际情况可能更复杂
        switch (mysqlDataType) {
//            case Types.BIT:
//                return "BOOLEAN";
            case Types.TINYINT:
                return "SMALLINT";
            case Types.BIGINT:
                return "BIGINT";
            case Types.INTEGER:
                return "INTEGER";
            case Types.DATE:
                return "timestamp";
            case Types.DOUBLE:
                return "NUMERIC";
            case Types.NUMERIC:
                return "NUMERIC";
            case Types.DECIMAL:
                return "NUMERIC";
            case Types.LONGVARCHAR:
                return "text";
            case Types.TIMESTAMP:
                return "TIMESTAMP";
            case Types.BLOB:
                return "BYTEA";
            // 其他类型...
            default:
                return "varchar";
        }
    }

    private static void copyTableData(String tableName, Connection mysqlConn, Connection pgsqlConn) throws SQLException {
        String selectAllDataSql = "SELECT * FROM " + tableName;
        PreparedStatement mysqlPs = mysqlConn.prepareStatement(selectAllDataSql);
        ResultSet dataRs = mysqlPs.executeQuery();
        ResultSetMetaData metaData = dataRs.getMetaData();
        StringBuilder insertSql = new StringBuilder("INSERT INTO ").append(tableName).append(" VALUES (");

        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            insertSql.append("?");
            if (i < metaData.getColumnCount()) {
                insertSql.append(",");
            }
        }
        insertSql.append(")");

        PreparedStatement pgsqlPs = pgsqlConn.prepareStatement(insertSql.toString());
        while (dataRs.next()) {
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                pgsqlPs.setObject(i, dataRs.getObject(i));
            }
            pgsqlPs.addBatch();
        }
        pgsqlPs.executeBatch();
    }

    private static String escapeSingleQuotes(String str) {
        return str.replace("'", "''");
    }
}

swany
关注
  • 3
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql转换postgresql工具
03-09
国外大牛写的一个mysql数据库转换postgresql的脚本。亲试可以无错误运行。
数据库迁移工具mysql2postgresql
08-14
数据库转换工具 MySQL转换到postgresql
推荐开源项目：MySQL 到 PostgreSQL 数据转换工具——mysql-to-postgres
最新发布
gitblog_00043的博客
05-10 349
推荐开源项目：MySQL 到 PostgreSQL 数据转换工具——mysql-to-postgres 项目地址:https://gitcode.com/maxlapshin/mysql2postgres 在数据库迁移的旅程中，从一种类型的数据系统迁移到另一种类型往往是挑战重重的任务。然而，有了 mysql-to-postgres 这个开源工具，将你的 MySQL 数据库无缝转换为 Postgre...
dbschema:MySQLPostgreSQL模式迁移变得容易
05-27
dbschema dbschema是自动运行MySQL或PostgreSQL迁移的工具。 使用表，它可以保持先前迁移的状态，从而避免重复。 特征： 支持MySQL和PostgreSQL 可选的迁移前和迁移后查询（例如，更新权限） 可以将多个数据库中的多个迁移作为一个进行处理。 安装 安装dbschema # Install required packages apt-get update apt-get install --yes libpq-dev gcc python3-dev pip3 install dbschema 创建一个配置文件 创建文件~/.dbschema.yml并添加数据库配置。 创建迁移表 dbschema使用一个名为migrations_applied的表来跟踪已应用的迁移，以避免重复。 请参阅或的架构。 迁移文件夹结构 对于每个数据库，您需要有一个迁
MYSQL迁移到PostgreSQL
chenhou5403的博客
05-13 128
友情推荐开始 ALSQL是一个通用的数据库查询工具。它支持多种主流数据库、支持直连模式、有统一的使用体验、有强大的过滤功能、有更多数据展示模式、面向项目、为逆向而优化、并且免安装和免费。下载体验及更多图文介绍：http://www.alsql.com 友情推荐结束 最近有一个MYSQL库要迁...
MySQL 迁移postgresql_从MySQL迁移到PostgreSQL
weixin_36435442的博客
01-18 233
We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons.Has anyone else done such a move? Our database is th...
Mysql迁移Postgresql
qq_33445829的博客
03-07 2103
Mysql迁移PG shell封装mysql导表PG copy mysql
mysql 迁移到postgresql
蓝色布鲁斯
12-20 760
<br />http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL
mysql->迁移postgresql
AinUser的博客
06-20 854
1.locate(a,b)-&gt;position(a in b)2.convert()函数不可以使用3.ifnull(null,0.00)-&gt;COALESCE(null,0.00)4.timestampadd(hour,24,jysj)-&gt;jysj::timestamp+'24 hour'5.substr()-&gt;substring()6.from_unixtime(unix_...
MySQL数据迁移到postgresql必备手册.pdf
09-01
mysql数据迁移到postgresql最简单的方式，亲测可以成功迁移
将Django使用的数据库从MySQL迁移到PostgreSQL的教程
09-10
在将Django应用程序的数据库从MySQL迁移到PostgreSQL的过程中，有几个关键步骤和注意事项需要了解。首先，你需要确保你的Django项目已经配置好支持两种数据库。在`settings.py`文件中，你需要添加两个数据库配置，一...
pgloader异构迁移mysql到postgres
08-23
《pgloader异构迁移MySQL到PostgreSQL详解》 数据库迁移是一项复杂且重要的任务，尤其是在企业级应用中，数据的安全性和完整性是至关重要的。pgloader作为一款强大的异构数据库迁移工具，特别适用于将MySQL数据库的...
mysql2pgsql.zip
07-14
在特定场景下，用户可能需要将数据从MySQL迁移至PostgreSQL，这时就需要用到数据迁移工具。"mysql2pgsql.zip"正是这样一款由国外专家开发的脚本，它能够帮助用户无缝地将MySQL数据库转换为PostgreSQL格式。 **MySQL...
mysql2pgsql.perl
10-10
mysql迁移到postgresql工具 mysql2pgsql.perl,支持环境 Linux / Mac OS / BSD / Solaris
mysql语句转postgres的工具
03-04
MySQL到PostgreSQL的迁移是一项常见的任务，特别是在企业级应用中，由于PostgreSQL的特性或业务需求，可能需要将数据从MySQL迁移到PostgreSQL。在这个过程中，`MysqlToPostgresUtil`工具扮演着重要的角色，它能帮助...
MySql数据库字段复制迁移程序
云客的技术博客【云游天下 做客四方】
07-22 1095
将本YK文件夹传入网站根目录 在config.php里面输入配置文件 访问：http://您的域名/yk/index.php 按照页面提示输入相关参数 点击开始导入即可 页面会有进度提示 程序在运行时会不停发送js语句告诉你进度情况 部分运行环境可能不会出现提示，好像浏览器假死一样，此时请耐心等待，程序正在后台运行，完成后会给出提示，切勿关闭浏览器 不论数据库是何种编码均可使
从 MySQL 迁移到 PostgreSQL 方案调研
Hehuyi_In的博客
11-28 3520
之前的文章PostgreSQL 初识和PostgreSQL 数据类型大致的把 PostgreSQL 了解了一下，那么接下来就是真正地把它用起来。 PGLoader 开源迁移工具，通过一行命令即可做到无缝迁移 https://github.com/dimitri/pgloader PGLoader 原本可以将不同数据源导入到 PostgreSQL 数据库，MySQL 只是它支持的一种。...
MySQL迁移到PostgreSQL操作指南
日拱一卒无有尽，功不唐捐终入海
08-05 9027
请注意，这只是一个简单的指南，MySQL到PostgreSQL的迁移实际上可能涉及更多的细节和步骤，具体取决于您的数据库和应用程序的特定情况。迁移完成后，确保进行充分的测试和验证，以确保数据的一致性和应用程序的正常运行。如果您的应用程序与数据库直接交互，并使用了特定于MySQL的查询语法或函数，那么您需要相应地修改这些代码，以适应PostgreSQL的语法和函数。请注意，MySQL和PostgreSQL之间有一些语法和数据类型的差异，您可能需要根据具体情况进行一些调整和转换。
用go语言同步MySQL表结构到postgresql数据库
06-07
可以使用以下步骤将MySQL表结构同步到PostgreSQL数据库中： 1. 使用Go语言的MySQL驱动和PostgreSQL驱动分别连接MySQL数据库和PostgreSQL数据库。 2. 查询MySQL数据库中需要同步的表结构信息，可以使用SHOW CREATE TABLE语句获取表结构信息。 3. 解析MySQL表结构信息，并将其转换为PostgreSQL的表结构信息，比如将MySQL的VARCHAR转换为PostgreSQL的TEXT。 4. 使用Go语言的PostgreSQL驱动在PostgreSQL数据库中创建新的表结构。 完整代码示例如下： ``` package main import ( "database/sql" "fmt" "log" _ "github.com/go-sql-driver/mysql" _ "github.com/lib/pq" ) func main() { // connect to MySQL mysqlDB, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database") if err != nil { log.Fatal(err) } defer mysqlDB.Close() // connect to PostgreSQL postgresDB, err := sql.Open("postgres", "postgres://user:password@localhost/postgres?sslmode=disable") if err != nil { log.Fatal(err) } defer postgresDB.Close() // query MySQL table structure rows, err := mysqlDB.Query("SHOW CREATE TABLE mytable") if err != nil { log.Fatal(err) } defer rows.Close() // parse MySQL table structure and convert to PostgreSQL table structure var tableName string var createTableStmt string for rows.Next() { err := rows.Scan(&tableName, &createTableStmt) if err != nil { log.Fatal(err) } } if err := rows.Err(); err != nil { log.Fatal(err) } postgresTableStmt := convertMySQLToPostgreSQL(createTableStmt) // create new table in PostgreSQL _, err = postgresDB.Exec(postgresTableStmt) if err != nil { log.Fatal(err) } fmt.Println("Table structure synced from MySQL to PostgreSQL") } func convertMySQLToPostgreSQL(mysqlTableStmt string) string { // TODO: implement conversion logic return mysqlTableStmt } ``` 在上述代码中，需要根据实际情况修改MySQL和PostgreSQL的连接信息，以及需要同步的表名和表结构信息。同时，还需要实现convertMySQLToPostgreSQL函数，用于将MySQL的表结构信息转换为PostgreSQL的表结构信息。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值