数据库数据迁移

半自动化迁移MSSQL数据到MySQL

MSSQL, MySQL, Pyetl, 数据库, 数据迁移

本次数据迁移的流程：

  • 手动流程：导出SQLServer的DDL，然后翻译成MySQL的DDL并且在MySQL中执行
  • 自动流程：使用Pyetl完成数据的迁移

翻译DDL

到处SQLServer中的DDL可以通过编写命令获得，也可以使用SQLServer的管理工具获得，其中管理工具最方便且靠谱，下面是使用该方式的教程：

如果你要使用命令导出，可以参考：https://stackoverflow.com/a/20350000/6364963。

接下来就是手动翻译导出的脚本了，下面是MSSQL类型与MySQL类型的一些转换关系（摘自MySQL 5.7官方文档）：

MSSQL Type MySQL Type Comment
INT INT
TINYINT TINYINT UNSIGNED flag set in MySQL，MySQL： -128到127(SIGNED)，0到255(UNSIGNED)，MSSQL默认就是无符号的。
SMALLINT SMALLINT
BIGINT BIGINT
BIT TINYINT(1)
FLOAT FLOAT Precision value is used for storage size in both
REAL FLOAT
NUMERIC DECIMAL
DECIMAL DECIMAL
MONEY DECIMAL
SMALLMONEY DECIMAL
CHAR CHAR/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have CHAR columns with a length up to 255 characters. Anything larger is migrated as LONGTEXT
NCHAR CHAR/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, character set of strings depend on the column character set instead of the datatype.
VARCHAR VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types.
NVARCHAR VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, character set of strings depend on the column character set instead of the datatype. 如果是NVARCHAR(max) 其长度为2G，需要MySQL里面的LONGTEXT
DATE DATE
DATETIME DATETIME
DATETIME2 DATETIME Date range in MySQL is ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’. Note: fractional second values are only stored as of MySQL Server 5.6.4
SMALLDATETIME DATETIME
DATETIMEOFFSET DATETIME
TIME TIME
TIMESTAMP TIMESTAMP
ROWVERSION TIMESTAMP
BINARY BINARY/MEDIUMBLOB/LONGBLOB Depending on its length
VARBINARY VARBINARY/MEDIUMBLOB/LONGBLOB Depending on its length
TEXT VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length
NTEXT VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length
IMAGE TINYBLOB/MEDIUMBLOB/LONGBLOB Depending on its length
SQL_VARIANT not migrated There is not specific support for this datatype.
TABLE not migrated There is not specific support for this datatype.
HIERARCHYID not migrated There is not specific support for this datatype.
UNIQUEIDENTIFIER VARCHAR(64) A unique flag set in MySQL. There is not specific support for inserting unique identifier values.
SYSNAME VARCHAR(160)
XML TEXT

一些转换过程中的问题：

  • identity 转化为 auto increment，如果identity设置了起始值，则需要在MySQL表上设置auto_increment = start_num,如果indentity设置了步长，则只能在MySQL存储引擎上设置，MySQL不支持单表的步长

翻译的时候结合正则表达式会快很多，我本次翻译120多张表总共花了差不多一个小时。翻译完成之后在MySQL中执行一下，我们就建立好了Schema了。接下来开始迁移数据。

如果觉得全部手动翻译太麻烦，可以借助以下工具完成一个初步的翻译然后自己再检验修改：

调整字符编码集

需要使MySQL的字符集和SQLServer一致，查看MSSQL字符集SELECT SERVERPROPERTY(N'Collation')，结果为Chinese_PRC_CI_AS，这里要注意一点，SQLServer的Collation（排序规则）不仅仅是排序规则，同时也设定了字符集。这一点和MySQL不同（MySQL字符集和排序规则是分开的）。下面是MySQL的字符集相关命令：

SHOW CHARACTER SET; --查看所有支持的字符集及其默认排序规则
SHOW COLLATION; --查看所有排序规则
SHOW VARIABLES LIKE 'character%'; --查看系统设置的字符集及其默认排序规则
SHOW VARIABLES LIKE 'collation%'; --查看系统设置的排序规则

MSSQL中的Chinese_PRC_CI_AS表示UNICODE字符集，我们需要在MySQL中使用utf-8即可，至于排序规则就先使用utf-8默认的排序规则。修改MySQL字符集可以使用set命令也可以修改my.ini配置文件并且重启服务器，具体方式可以参考：http://database.51cto.com/art/201010/229167.htm ，修改后不会影响已经存在的表和列，因此可能需要修改已存在的库，表和列的字符编码：http://www.jianshu.com/p/a18269a4870e 。注意set只在当前连接中有效，且列的默认编码继承表，表继承库。

迁移数据

建立好完整的Schema之后就可以开始迁移数据了。数据迁移的方式有很多种，下面是我用过的一些方式：

  • 编写程序，同时连接两边的数据库，从一边读取，一边插入。该方式主要缺点是迁移速度慢，数据量较大时可能几个小时才能完成一次迁移（笔者一张800W数据的表迁移了4个多小时，当然当天的网络也比较慢），迁移速度低的主要原因是网络以及INSERT语句的开销。
  • 从一个数据库导出文件，然后拷贝到另一个数据库中去加载。该方法不受网速影响，同时导出和导入速度都很快。主要缺点是数据库都要支持导入导出功能且格式相同。比如：MSSQL导出文本文件的功能有限，不支持导出CSV，导出SQL文件也需要自己编写存储过程实现。而MySQL则同时支持.SQL文件和CSV的导入导出

笔者本次迁移的数据库大小接近6G，因此采用文件的方式导出导入，由于MSSQL本身功能有限，且手动导出流程繁琐，故使用pyetl程序来完成文件的导出和导入这一步。值得一提的是，pyetl支持多种不同关系型数据库文件以及CSV文件中数据的抽取，转换和迁移; 同时也支持多种模式的迁移，速度也非常快。本工具由笔者编写，有任何使用上的问题均可提出。

参考资料

网上有关MSSQL导出文本文件的资料，可以发现所有方式都不支持分隔符转义：

MSSQL 不支持CSV的官方声明：

MySQL为什么LOAD FILE比INSERT更快：

分享到