数据库 - 记一次 MySQL 到 SQL Server 的项目迁移

记一次 MySQL 到 SQL Server 的项目迁移

在最近的工作中，遇到了一个数据库迁移的需求，即把某个系统的数据库从 MySQL 迁移到 SQL Server 。特此记录。

准备#

工具下载#

打开下面的网址，下载  SQL Server 官方的迁移工具：

https://www.microsoft.com/en-us/download/details.aspx?id=54257

第一个是64位版的，x86是32位版的。我用到的是第一个。

若缺失Microsoft.NetFramework 4.5.2可在该地址下载：

https://www.microsoft.com/en-us/download/confirmation.aspx?id=42642

安装#

双击刚下载好的安装包进行安装，如果没有安装 MySql ODBC driver，就点击页面上的 download 下载 ODBC安装包。

退出SSMA安装程序，先安装 My SQL ODBC driver。安装成功之后再打开 SSMA 安装程序进行安装。

 

使用 SSMA 迁移数据库#

开始迁移#

打开 SSMA

新建工程

选择你要迁移的目标数据库服务器

连接 My SQL ，选择需要同步的数据库

 

选择要迁移的数据库，右键单击数据库名称，选择 Create Report ：

在我待迁移的 My SQL 生成的 Report 中，报了一个 Error 和两个 Warning：

这也直接导致了迁移预计时间高达154小时，这就有点难以接受了，需要把这三个问题解决掉。

错误处理#

其实第一个 Error 和 Warning 中的第二个 Primary key size exceeded 是一个问题，就是 Quartz 框架在 My SQL 数据库中生成的组合主键/索引，在 SQL Server 中过大了：

M2SS0020: The size of the index key is more than 900 bytes
M2SS0019: The size of the primary key is more than 900 bytes

我这边根据项目实际情况考虑是， Quartz 相关表就不迁移了，原因下面会讲到。

而第二个 Warning 则是 My SQL 版本引起的：

M2SS0183: The following SQL clause was ignored during conversion: COLLATE utf8mb4_0900_ai_ci.

utf8mb4_0900_ai_ci 在 My SQL 8以下是不被支持的，导出的数据库需要修改 utf8mb4_0900_ai_ci 为 utf8mb4_unicode_ci 或者 utf8mb4_general_ci。这里理论上可以通过修改DDL文件，把collate 的属性值从 utf8mb4_0900_ai_ci 修改为utf8_general_ci，DEFAULT CHARSET 的属性值从 utf8mb4 修改为 utf8。但我改了几次没好用，就直接用 My SQL Workbench 手动修改了。很难受。

继续迁移#

连接到 SQL Server

转换模式(即表) 在 My SQL 的元数据资源管理器中 ，右键单击要迁移的数据库，然后选择 " Convert Schema ":

将转换的表同步到SQL Server：

从 My SQL 迁移数据：

备注：

直接迁移会出现下面的崩溃

解决办法：

设置之后，迁移数据就可以操作成功了。

迁移中遇到的方案变更#

在初步设想中，本想把一整套 nacos 注册的微服务迁移到 SQL Server 中去。但在数据库迁移的过程中，发现了两个问题。

Nacos 只支持 My SQL#

此段摘自：https://www.cnblogs.com/xixisix/p/15821706.html

Nacos 的数据源获取都是通过 com.alibaba.nacos.config.server.service.datasource.DynamicDataSource 来获取的，在获取数据源时,根据配置判断你到底是使用内置的本地数据库还是外部的数据库(My SQL)

复制代码
public synchronized DataSourceService getDataSource() {
    try {

        // Embedded storage is used by default in stand-alone mode
        // In cluster mode, external databases are used by default
        // 根据System.getProperty("nacos.standalone")来判断你到底是不是standalone模式
        // standalone模式,使用内置数据库
        if (PropertyUtil.isEmbeddedStorage()) {
            if (localDataSourceService == null) {
                localDataSourceService = new LocalDataSourceServiceImpl();
                localDataSourceService.init();
            }
            return localDataSourceService;
        } else {
            // 如果不是standalone,直接创建外部的数据源
            if (basicDataSourceService == null) {
                basicDataSourceService = new ExternalDataSourceServiceImpl();
                basicDataSourceService.init();
            }
            return basicDataSourceService;
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
复制代码

外部数据源com.alibaba.nacos.config.server.service.datasource.ExternalDataSourceServiceImpl.init()

复制代码
@Override
public void init() {
    queryTimeout = ConvertUtils.toInt(System.getProperty("QUERYTIMEOUT"), 3);
    jt = new JdbcTemplate();
    // Set the maximum number of records to prevent memory expansion
    jt.setMaxRows(50000);
    jt.setQueryTimeout(queryTimeout);

    testMasterJT = new JdbcTemplate();
    testMasterJT.setQueryTimeout(queryTimeout);

    testMasterWritableJT = new JdbcTemplate();
    // Prevent the login interface from being too long because the main library is not available
    testMasterWritableJT.setQueryTimeout(1);

    //  Database health check

    testJtList = new ArrayList<JdbcTemplate>();
    isHealthList = new ArrayList<Boolean>();

    tm = new DataSourceTransactionManager();
    tjt = new TransactionTemplate(tm);

    // Transaction timeout needs to be distinguished from ordinary operations.
    tjt.setTimeout(TRANSACTION_QUERY_TIMEOUT);
    // 判断到底是是不是用外部数据库
    // 这个可以在com.alibaba.nacos.config.server.utils.PropertyUtil#loadSetting中看到
    // setUseExternalDB("mysql".equalsIgnoreCase(getString("spring.datasource.platform", "")));
    // 好家伙,直接判断配置的是不是mysql,是mysql那就是外部数据库,进行reload,不是,那就不管了
    if (PropertyUtil.isUseExternalDB()) {
        try {
            reload();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(DB_LOAD_ERROR_MSG);
        }

        if (this.dataSourceList.size() > DB_MASTER_SELECT_THRESHOLD) {
            ConfigExecutor.scheduleConfigTask(new SelectMasterTask(), 10, 10, TimeUnit.SECONDS);
        }
        ConfigExecutor.scheduleConfigTask(new CheckDbHealthTask(), 10, 10, TimeUnit.SECONDS);
    }
}
复制代码

在com.alibaba.nacos.config.server.service.datasource.ExternalDataSourceServiceImpl#reload中,我们可以看到

复制代码
@Override
public synchronized void reload() throws IOException {
    try {
        // 根据配置文件,构建数据源集合
        dataSourceList = new ExternalDataSourceProperties()
            .build(EnvUtil.getEnvironment(), (dataSource) -> {
                JdbcTemplate jdbcTemplate = new JdbcTemplate();
                jdbcTemplate.setQueryTimeout(queryTimeout);
                jdbcTemplate.setDataSource(dataSource);
                testJtList.add(jdbcTemplate);
                isHealthList.add(Boolean.TRUE);
            });
        new SelectMasterTask().run();
        new CheckDbHealthTask().run();
    } catch (RuntimeException e) {
        FATAL_LOG.error(DB_LOAD_ERROR_MSG, e);
        throw new IOException(e);
    }
}
复制代码

在com.alibaba.nacos.config.server.service.datasource.ExternalDataSourceProperties#build中

复制代码
List<HikariDataSource> build(Environment environment, Callback<HikariDataSource> callback) {
    List<HikariDataSource> dataSources = new ArrayList<>();
    // 把胚子信息绑定到当前的ExternalDataSourceProperties对象,赋值操作
    // 因为外面是直接new出来的,需要对属性根据文件进行赋值
    Binder.get(environment).bind("db", Bindable.ofInstance(this));
    Preconditions.checkArgument(Objects.nonNull(num), "db.num is null");
    Preconditions.checkArgument(CollectionUtils.isNotEmpty(user), "db.user or db.user.[index] is null");
    Preconditions.checkArgument(CollectionUtils.isNotEmpty(password), "db.password or db.password.[index] is null");
    // 可以配置多个数据库
    for (int index = 0; index < num; index++) {
        int currentSize = index + 1;
        Preconditions.checkArgument(url.size() >= currentSize, "db.url.%s is null", index);
        // 拿到spring.datasource.xxx一堆,这个针对所有的数据源都适用
        DataSourcePoolProperties poolProperties = DataSourcePoolProperties.build(environment);
        // 为每一个数据源进行单独的url,user,password进行替换
        poolProperties.setDriverClassName(JDBC_DRIVER_NAME);
        poolProperties.setJdbcUrl(url.get(index).trim());
        poolProperties.setUsername(getOrDefault(user, index, user.get(0)).trim());
        poolProperties.setPassword(getOrDefault(password, index, password.get(0)).trim());
        HikariDataSource ds = poolProperties.getDataSource();
        ds.setConnectionTestQuery(TEST_QUERY);
        dataSources.add(ds);
        callback.accept(ds);
    }
    Preconditions.checkArgument(CollectionUtils.isNotEmpty(dataSources), "no datasource available");
    return dataSources;
}
复制代码

这个整体还行,但是为啥JDBC_DRIVER_NAME是硬编码呢,代码中清晰看到

private static final String JDBC_DRIVER_NAME = "com.mysql.cj.jdbc.Driver";

到这已经一目了然,代码中硬编码了mysql,driver也没法改,所以根本没法更换数据库驱动,有点骚,而且com.mysql.cj.jdbc.Driver是mysql8的驱动,对mysql版本是有要求的

再看其他部分,也可以发现大量的硬编码,例如 com.alibaba.nacos.config.server.auth.ExternalUserPersistServiceImpl 中，几乎所有的sql都是硬编码。所以要改造成其他数据库工作量还是非常大的

Quartz 框架表结构迁移#

由于项目中还使用了 Quartz 框架，所以本想在结构迁移时将 Quartz 相关表结构一并迁移到 SQL Server中。但在表迁移过程中，遇到了上文中提到的错误和警告。
所以综合下列考虑：
  • 由于使用了nacos作为注册中心，所以服务器中必定存在My SQL
  • Quartz 框架与业务弱相关，可以看成另一个独立于整体业务框架的第三方服务
  • 理论上 Quartz 可以和 SQL Server 使用，但涉及到表迁移的话，可能存在不兼容风险，且解决起来需要时间，可能会延长项目周期

决定不迁移 Quartz。

Spring Boot 连接 SQL Server#

POM文件中引入 mssql 驱动依赖#

            <dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>mssql-jdbc</artifactId>
                <version>7.4.1.jre8</version>
            </dependency>

配置yml文件#

复制代码
spring:
  datasource:
    druid:
      stat-view-servlet:
        enabled: true
        loginUsername: admin
        loginPassword: 123456
    dynamic:
      druid:
        initial-size: 5
        min-idle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        # mysql 用这个
        # validationQuery: SELECT 1 FROM DUAL
        # sql server 用 mysql 版本的参数的话，会报错：com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 ‘DUAL‘ 无效。
        # sql server 用这个
        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filters: stat,slf4j
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      datasource:
        master:
          #  sql server
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          url: jdbc:sqlserver://localhost:1433;SelectMethod=Cursor;DatabaseName=数据库名;
          username: 用户名
          password: 密码
复制代码

SQL语句后续兼容问题#

mybatis 在saveBatch的时候 报错 The statement must be executed before any results can be obtained#

或

mybatis plus 报错com.microsoft.sqlserver.jdbc.SQLServerException: 必须执行该语句才能获得结果#

原因：

从 MyBatis3.3.1版本开始，MyBatis开始支持批量新增回写主键值的功能，这个功能首先要求数据库主键值为自增类型,同时还要求该数据库提供的 JDBC 驱动可以支持返回批量插入的主键值（JDBC提供了接口,但并不是所有数据库都完美实现了该接口），因此到目前为止,可以完美支持该功能的仅有MySQL数据库。由于SQL Server数据库官方提供的 JDBC 只能返回最后一个插入数据的主键值,所以不能支持该功能。

解决：

1.数据表主键使用idworker：idworker自动生成主键，能够会填主键，而自增不行。

2.自定义批量新增语句

复制代码
// 设置useGeneratedKeys为true，返回数据库自动生成的记录主键id
<insert id="batchInsert"  useGeneratedKeys="true" keyProperty="id">
    insert into xxx (x,y)
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.x},
        #{item.y}
        )
    </foreach>
</insert>
复制代码

在SQL Server中使用 FOR UPDATE 报错：Failure occured during job recovery. [See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: 第 1 行: 只有 DECLARE CURSOR 才允许使用 FOR UPDATE 子句。 [See nested exception: com.microsoft.sqlserver.jdbc.SQLServerException: 第 1 行: 只有 DECLARE CURSOR 才允许使用 FOR UPDATE 子句。]]#

mssql 的链接参数 

url: jdbc:jtds:sqlserver://ip:1433;useCursors=true;DatabaseName=db

把 "SelectMethod=Cursor"  改成 "useCursors=true" 即可

SQL Server 报错：操作数类型冲突: varbinary 与 float 不兼容#

当 insert into table (id, xx……) values(……)，如果插入值有 null 值并且 table 表中字段类型是 float，则报 操作数类型冲突: varbinary 与 float 不兼容。

解决方案： 不要在 float 类型的字段插入 null 就好了。（但这个在 MySQL 数据库没有影响。）

SQL Server 没有 double 类型#

My SQL 有 double 类型，但是 SQL Server 没有。

  • 如果需要用双精度数据，且不固定小数位，用 float 就可以了
  • 如果是固定小数位，可以用 numric 
  • 如果整数和小数都出现，可以用 real 

SQL Server 中 FIND_IN_SET 函数不可用#

用

charindex(cast(匹配列 as varchar(50)), 被匹配列（多个用，分开的值）) <![CDATA[ > ]]> 0

替换 MySQL 中的 FIND_IN_SET

date_format() 函数在 SQL Server 中不可用#

用

FORMAT(time,'yyyy-MM-dd')

代替

date_format(time, '%y%m%d')

%y%m%d 在 SQL Server 里解析出来不是 220101 且月份不对，m得换成大写或者 yyyy-MM-dd

SQL Server 报错：选择列表中的列 'table.column' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中#

SQL Server 的 group by 必须包含返回的所有字段。

ifnull 函数在 SQL Server 中不可用#

用 SQL Server 中的 

ISNULL(字段, '')

代替

limit 关键字在 SQL Server 中不可用#

limit x 关键字在 SQL Server 中不可用，要在 select 关键字后面加 top x

sysdate() 函数在 SQL Server 中不可用#

My SQL 中的 sysdate() 在 SQL Server 中要换成 Sysdatetime()

SQL Server 不支持 replace into 关键字#

目前没有找到替代方案，所以在项目中把相关逻辑迁移到了 service 层

group_concat 函数在 sql server 中不可用#

目前没找到替代方案，所以在项目中把 group_concat 的逻辑迁移到了 service 层

 

posted @   Helios_Fz  阅读(1121)  评论(0)  编辑  收藏  举报
相关博文：
· 数据库 - MySQL转换SQL Server时，替换 FIND_IN_SET 函数引发的问题
· Docker - Docker 离线搭建单机 Nacos 集群
· 记一次MySQL数据迁移到SQLServer全过程
· 通过MySQL Workbench 将 SQL Server 迁移到GreatSQL
· 从 Oracle 到 MySQL 数据库的迁移之旅
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！
点击右上角即可分享
微信分享提示
主题色彩