1. 概述

由于历史原因,工作上,需要把Microsoft SQL Server指定数据库上定时更新的数据,同步到MySQL 8,大概一周一次。

解决方案有几个:

  • 1)采用现成的工具。

    • 但是,一时间没找到好的工具。
  • 2) 开发个程序,读取SQL Server的相关数据,再插入到MySQL。

    • 需要时间开发,且功能上具有针对性。
    • 作为长期使用的工具,这是最优的方案。
  • 3) 从SQL Server生成MySQL的insert语句,再到MySQL上执行。

    • 很多数据库管理工具都提供了数据迁移功能,例如:MySQL Workbench。
    • 如果数据结构不变,insert语句是稳定的,这方案也不错。
    • 要注意采用批量插入,提高导入性能。
  • 4) 从SQL Server导出格式化数据,例如CSV文件,再导入到MySQL。

    • 由于保存数据的CSV文件比较通用,相关的数据库管理工具都支持导入导出。
    • 但是要注意要处理数据格式、NULL数据等问题。

目前采用了第3个方案,CSV文件比较通用,也不用考虑怎么开发。但是偶尔会出现导入MySQL失败的问题,比如出现了NULL数据。后面应该会写个程序处理,直接生成insert语句。

2. SQL Server导入导出CSV

一般使用BCP命令。即Bulk Copy Program,是一个命令行工具,用于在SQL Server之间批量传输数据。由于微软推出了SQL Server for Linux,所以可以完全在Linux执行导入导出的操作。另外,微软提供了SQL Server for Linux的官方Docker镜像,比Windows上安装SQL Server Express更方便,非常适合开发测试使用(主要应付历史)。

官方介绍及参考文档如下:

2.1. BCP使用说明

直接运行bcp命令,会提示其用,如下:

> C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
  [-m 最大错误数]             [-f 格式化文件]       [-e 错误文件]
  [-F 首行]                   [-L 末行]             [-b 批大小]
  [-n 本机类型]               [-c 字符类型]         [-w 宽字符类型]
  [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q 带引号的标识符]
  [-C 代码页说明符]           [-t 字段终止符]       [-r 行终止符]
  [-i 输入文件]               [-o 输出文件]         [-a 数据包大小]
  [-S 服务器名称]             [-U 用户名]           [-P 密码]
  [-T 可信连接]               [-v 版本]             [-R 允许使用区域设置]
  [-k 保留 Null 值]           [-E 保留标识值]
  [-h"加载提示"]              [-x 生成 xml 格式化文件]
  [-d 数据库名称]

2.2. BCP导出CSV文件

CMD批处理命令参考如下:

set CUR_PATH=%~dp0
set BCP="C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe"
set BCP_PARAM=-S "IP,端口" -U "用户名" -P "密码" -d "数据库名" -t \t -b 1000 -c -C 65001 -k

rem 导出指定数据库表的数据
%BCP% 数据库名.dbo.表名 out %CUR_PATH%table_export.csv %BCP_PARAM%

rem 导出指定查询语句的数据
%BCP% "select查询语句" queryout %CUR_PATH%query_export.csv %BCP_PARAM%

参数说明:

  • -S 服务器名称,服务器IP与端口之间,使用英文逗号(即“,”)分隔。
  • -t 字段终止符,默认是Tab符号(即“/t”)。
  • -b 批大小,如果导出数据太多,需要分页操作,默认是1000。
  • -c 字符类型,设置导出文件的字符编码为UTF-8时,设置为“-c -C 65001”,要注意大小写。

要注意,导出的CSV文件不带字段名称。需要记录字段名称时,目前只能把字段名称插入到CSV文件的第一行,并且以数据行的分隔符号进行分隔。

2.3. BCP导入CSV文件

要先创建对应的表,才能执行导入。格式如下:

set BCP_PARAM=-S "IP,端口" -U "用户名" -P "密码" -d "数据库名" -t \t -b 1000 -c -C 65001 -k
bcp 数据库名.dbo.表名 in 数据文件.csv %BCP_PARAM%

2.4. SQL语句执行BCP命令

要注意,用户需要授权可执行xp_cmdshell的权限。官方详细说明如下:xp_cmdshell (Transact-SQL) - SQL Server | Microsoft Learn

格式如下:

exec master..xp_cmdshell 'bcp ...'

2.5. SQL Server的其它导入导出方案

3. MySQL导入导出CSV

3.1. 相关参考

参考文章:MySQL导出数据为CSV的方法

MySQL官方文档:

3.2. MySQL导入CSV

假如导入数据到数据表user_table,该表有字段id、name、remark,其中remark数据可能为NULL。

-- 导入CSV文件的SQL语句
load data infile '/var/lib/mysql-files/import_data.csv' into table user_table fields terminated by '\t' escaped by '' optionally enclosed by '"' lines terminated by '\n' ignore 1 lines (id,name,@remark) set remark=nullif(@remark,'');

说明:

  • CSV文件需要放在mysql用户有权限的目录,比如/var/lib/mysql-files/
  • fields terminated by '\t',表示CSV数据以TAB符号分隔。
  • escaped by '',设置转义字符,默认的是反斜杠(backslash:\ ),设置空值('')表示不适用转义。
  • optionally enclosed by '"',以双引号包裹单一字段的数据。
  • lines terminated by '\n',每行数据的结束符号。
  • ignore 1 lines,导入数据时跳过第一行,因为第一行是字段名称的说明。
  • (id,name,@remark),把一行数据关联到对应的字段。其中@remark是把数据赋值到变量,后面有特殊处理。
  • remark=nullif(@remark,''),表示remark字段的数据,根据@remark变量进行处理。这里是NULL数据转为空字符串。

3.3. MySQL导出CSV

-- 导出CSV文件的SQL语句
select * from user_table into outfile '/tmp/expor_data.csv' fields terminated by '\t' escaped by '\\'  optionally enclosed by '"' lines terminated by '\n' ;

说明:

  • fields terminated by "\t",表示CSV数据以TAB符号分隔。
  • escaped by '\\',设置转义字符,默认的是反斜杠(backslash:\ ),设置空值('')表示不适用转义。
  • optionally enclosed by '"',以双引号包裹单一字段的数据。
  • lines terminated by '\n',每行数据的结束符号。

4. SQL语言的concat_ws函数

SQL的select语句可以使用concat_ws函数,可以实现一行数据的所有字段值合并成一个字符串,并指定分隔符号。然后把查询结果保存为文本文件(包括CSV),即实现了数据导出。参考文档如下:

标签: SQL Server, MySQL

添加新评论