Migrate Data Between Different Databases with CSV
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更方便，非常适合开发测试使用（主要应付历史）。
官方介绍及参考文档如下：
- Import and export bulk data using bcp (SQL Server)
https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-and-export-bulk-data-by-using-the-bcp-utility-sql-server - 在 Linux 上安装 SQL Server 命令行工具 sqlcmd 和 bcp
https://learn.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup-tools - SQL Server中bcp命令的用法以及数据批量导入导出
https://www.cnblogs.com/xwdreamer/archive/2012/08/22/2651180.html
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的其它导入导出方案
- 1）使用SQL Server Management Studio。
- 2）使用sqlcmd命令，参考官方说明：https://learn.microsoft.com/zh-cn/sql/tools/sqlcmd-utility
3. MySQL导入导出CSV
3.1. 相关参考
参考文章：MySQL导出数据为CSV的方法
MySQL官方文档：
- MySQL :: MySQL 8.0 Reference Manual :: 7.2 Database Backup Methods
- MySQL :: MySQL 8.0 Reference Manual :: 13.2.9 LOAD DATA Statement
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），即实现了数据导出。参考文档如下：