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

添加新评论