数据库 - 记一次 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 框架表结构迁移
- 由于使用了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 层
