Mysql转Sqlserver建表脚本（基于java8）

最新推荐文章于 2024-07-28 03:27:16 发布

JesseXW

最新推荐文章于 2024-07-28 03:27:16 发布

阅读量659

点赞数 1

分类专栏：数据库文章标签： mysql sqlserver java

本文链接：https://blog.csdn.net/JesseXW/article/details/132336969

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

前言：公司要求自研的平台支持sqlserver数据库，所以需要将之前使用的mysql数据库转换为sqlserver的，以便后续平台也能基于sqlserver数据库运行。但是，sqlserver的注释写起来实在是太痛苦了，mysql一个comment就搞定的事情，sqlserver要写一大堆代码。虽然都是很简单的cv修改工作，但是时间不应该拿去摸鱼吗（bushi）？所以便诞生了这个脚本

废话不多说，直接上代码！

import java.util.ArrayList;
import java.util.List;


/**
 * 1.这是mysql转sqlserver的建表脚本
 * 2.仅需输入tableName表名,tableValue表名的注释,content表头信息即可
 * 3.最后的输出结果中。例如主键、索引、联合索引等均需手动添加
 * 4.如果是 建表文件 的 第一个 表则还需在输出的注释结果前加上以下代码(后续就不用加了):
 * DECLARE @CURRENT_SCHEMA varchar(64)
 * SET @CURRENT_SCHEMA = (select SCHEMA_NAME())
 *
 * @author: Jesse
 * @Date: 2023/8/17
 */
public class Mysql2Sqlserver {

    public static void main(String[] args) {
        //==========================================初始数据==================================================

        //输入表名
        String tableName = "";
        //输入表名注释
        String tableValue = "";
        //输入需要解析的表头信息
        String content = "";


        //=============================================开始解析===============================================
        System.out.println("/*==============================================================*/");
        System.out.println("/* Table: " + tableName + "                                         */");
        System.out.println("/*==============================================================*/");

        //======================建表语句=========================
        String convertedScript = convertScript(content);
        String begin = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = '" + tableName + "')\n" +
                "create table " + tableName + "\n" +
                "(\n";
        String finalBegin = begin + convertedScript + ")";
        System.out.println(finalBegin);
        System.out.println();

        //======================表名解析=========================
        String title = "";

        title += "IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',\n";
        title += "'SCHEMA', @CURRENT_SCHEMA,\n";
        title += "'TABLE', N'" + tableName + "',\n";
        title += "NULL, NULL)) > 0)\n";
        title += "EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'" + tableValue + "',\n";
        title += "@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,\n";
        title += "@level1type = 'TABLE', @level1name = N'" + tableName + "'\n";
        title += "ELSE\n";
        title += "EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'" + tableValue + "',\n";
        title += "@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,\n";
        title += "@level1type = 'TABLE', @level1name = N'" + tableName + "'\n";
        System.out.println(title);
        System.out.println();


        //======================表头注释解析=========================

        //按行分割内容
        String[] lines = content.split("\n");

        List<String> columnNamesList = new ArrayList<>();
        List<String> columnDescriptionsList = new ArrayList<>();
        String currentColumnName = null;
        //逐行解析内容
        for (String line : lines) {
            //去除行两侧的空白字符
            String trimmedLine = line.trim();
            if (trimmedLine.isEmpty()) {
                //如果行为空白，则跳过
                continue;
            }
            //将行分割为部分
            String[] parts = trimmedLine.split("\\s+");
            // 如果至少有两个部分，则可能是列定义的开头部分
            if (parts.length >= 2) {
                if (currentColumnName == null) {
                    // 如果当前列名尚未设置，则设置为第一个部分
                    currentColumnName = parts[0];
                }
                // 如果行包含 "comment"，则可能是列定义的结束部分
                if (line.contains("comment")) {
                    int startIndex = line.indexOf("comment") + 9;
                    int endIndex = line.lastIndexOf("'");
                    // 提取列描述，去除逗号但保留单引号
                    String columnDescription = line.substring(startIndex, endIndex);
                    columnNamesList.add(currentColumnName);
                    columnDescriptionsList.add(columnDescription);
                    currentColumnName = null;//重置当前列名
                }
            }
        }

        String[] columnNames = columnNamesList.toArray(new String[0]);
        String[] columnDescriptions = columnDescriptionsList.toArray(new String[0]);

        for (int i = 0; i < columnNames.length; i++) {
            String columnName = columnNames[i];
            String columnDescription = columnDescriptions[i];

            String script = "";

            script += "IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',\n";
            script += "'SCHEMA', @CURRENT_SCHEMA,\n";
            script += "'TABLE', N'" + tableName + "',\n";
            script += "'COLUMN', N'" + columnName + "')) > 0)\n";
            script += "EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'" + columnDescription + "',\n";
            script += "@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,\n";
            script += "@level1type = 'TABLE', @level1name = N'" + tableName + "',\n";
            script += "@level2type = 'COLUMN', @level2name = N'" + columnName + "'\n";
            script += "ELSE\n";
            script += "EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'" + columnDescription + "',\n";
            script += "@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,\n";
            script += "@level1type = 'TABLE', @level1name = N'" + tableName + "',\n";
            script += "@level2type = 'COLUMN', @level2name = N'" + columnName + "'";

            System.out.println(script);
            System.out.println();
        }
    }

    public static String convertScript(String inputScript) {
        String[] lines = inputScript.split("\n");
        StringBuilder result = new StringBuilder();
        boolean isPrimaryKey = false;

        for (String line : lines) {
            String convertedLine = removeComment(line).trim();

            if (convertedLine.startsWith("primary key")) {
                isPrimaryKey = true;
                convertedLine = convertedLine.replace("primary key (", "").replace(")", "").trim();
                result.append(convertedLine).append(" primary key,\n");
            } else if (!convertedLine.isEmpty()) {
                result.append(convertedLine).append("\n");
            }
        }

        return result.toString();
    }

    public static String removeComment(String line) {
        return line.replaceAll("comment\\s+'.*?'", "");
    }
}

附：使用参考

1.这是我的mysql建表脚本：

2.所以我需要在这个转换脚本输入信息如下：

3.表头信息直接复制mysql括号中的信息就ok，primary key那行不用复制

4.最后的输出效果：

/*==============================================================*/
/* Table: PLT_ZR_TEND_USER                                         */
/*==============================================================*/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = 'PLT_ZR_TEND_USER')
create table PLT_ZR_TEND_USER
(
ZR_ID                varchar(64) not null ,
TEND_ID              varchar(64) not null ,
TEND_NAME            varchar(256) not null ,
USER_ID              varchar(64) not null ,
USER_AC              varchar(128) not null ,
USER_NAME            varchar(256) not null ,
USER_TYPE            int not null default 4 ,
LEADER_ID            varchar(64) ,
LEADER_NAME          varchar(256) ,
)

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
NULL, NULL)) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'关系：租户-用户',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'关系：租户-用户',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER'


IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'ZR_ID')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'唯一编号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'ZR_ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'唯一编号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'ZR_ID'

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'TEND_ID')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'租户编号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'TEND_ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'租户编号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'TEND_ID'

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'TEND_NAME')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'租户企业',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'TEND_NAME'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'租户企业',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'TEND_NAME'

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'USER_ID')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'用户姓名',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'USER_ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'用户姓名',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'USER_ID'

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'USER_AC')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'用户账号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'USER_AC'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'用户账号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'USER_AC'

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'USER_NAME')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'用户编号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'USER_NAME'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'用户编号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'USER_NAME'

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'USER_TYPE')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'用户类型',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'USER_TYPE'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'用户类型',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'USER_TYPE'

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'LEADER_ID')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'上级领导编号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'LEADER_ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'上级领导编号',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'LEADER_ID'

IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', @CURRENT_SCHEMA,
'TABLE', N'PLT_ZR_TEND_USER',
'COLUMN', N'LEADER_NAME')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'上级领导姓名',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'LEADER_NAME'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'上级领导姓名',
@level0type = 'SCHEMA', @level0name = @CURRENT_SCHEMA,
@level1type = 'TABLE', @level1name = N'PLT_ZR_TEND_USER',
@level2type = 'COLUMN', @level2name = N'LEADER_NAME'

5.最后把primary key加在对应的位置即可

ps：

如果你是建表文件的第一个表的话一定要记得加注释中说的

DECLARE @CURRENT_SCHEMA varchar(64)
SET @CURRENT_SCHEMA = (select SCHEMA_NAME())

否则运行时会报错

加的位置like this：

如果你的sqlserver版本是最新版，可能就找不到 sysobjects 这个系统表了，在测试的时候删除这个判断即可

IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = 'PLT_ZR_TEND_USER')

注意在sqlserver中，mysql的 longtext 类型对应的是 text 或者 varchar(max) 哦，记得也改一下
可能会出现解析遗漏，比如 default 后面的值消失了啥的，自己多检查下吧

==================================================================================

以上，分分钟搞定几十个表，剩下的时间拿去xx不是美滋滋？

唯一不足就是每个表还是得一个一个输入表名，表头信息，不能批量识别，有大佬能二次开发也是极好的，反正已经比我之前cv慢慢改快了几十倍了，先摆一会。

JesseXW

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
Mysql转Sqlserver建表脚本（基于java8）

mysql转sqlserver建表脚本（基于java8）建表语句转换
复制链接

扫一扫

专栏目录