一、常见格式翻车现场

最近公司要搞数据迁移，小王信心满满地右键点击SQL Server Management Studio（SSMS）的导出向导，结果收到业务部门的连环call："导出的CSV打开全是乱码！""报表里的日期都变成数字了！""地址里的逗号把列都搞串了！"这些看似简单的格式问题，往往成为数据迁移路上的拦路虎。

1.1 CSV的"分列强迫症"

当我们用以下脚本导出带特殊字符的数据时：

-- 创建测试表（SQL Server 2019）
CREATE TABLE ExportDemo (
    OrderID INT PRIMARY KEY,
    CustomerName NVARCHAR(50),
    DeliveryAddress NVARCHAR(100),
    OrderDate DATETIME
);

INSERT INTO ExportDemo VALUES
(1, '张,三', '北京市朝阳区,奥运村街道', '2023-08-01'),
(2, '李"四', '上海市"浦东新区"世纪大道', GETDATE());

如果直接使用SSMS的"导出数据"向导生成CSV，用Excel打开会看到惨烈场景：

OrderID,CustomerName,DeliveryAddress,OrderDate
1,"张,三","北京市朝阳区,奥运村街道",2023-08-01
2,"李四","上海市浦东新区世纪大道",45321

地址中的逗号导致分列错误，双引号神秘消失，日期变成神秘数字——这简直是数据界的"恐怖片"。

1.2 Excel的"数字焦虑症"

日期时间类型直接导出为Excel的序列值（如45321），金额数字变成科学计数法，超过15位的数字（如身份证号）末尾变0。这些"智能"转换让业务人员直呼看不懂。

1.3 编码界的"巴别塔困境"

当我们尝试用BCP命令导出包含中文的数据：

bcp ExportDemo out C:\Export\data.csv -c -T -S localhost -U sa

打开文件却发现中文变成问号或乱码，就像收到了外星人的电报。

二、格式修复工具箱——手把手驯服数据

2.1 CSV格式救星：BCP的乾坤大挪移

# 使用BCP导出带格式控制的CSV（SQL Server 2019）
bcp "SELECT QUOTENAME(CustomerName,'""') AS CustomerName, 
           QUOTENAME(DeliveryAddress,'""') AS DeliveryAddress,
           CONVERT(VARCHAR(23), OrderDate, 121) 
    FROM TestDB.dbo.ExportDemo" 
queryout "C:\Export\data.csv" 
-c -C 65001 -t "||" -r "ENDROW" -S localhost -U sa -P 123456

参数详解：

  • QUOTENAME()：给字段套上防弹衣（双引号转义）
  • -C 65001：指定UTF-8编码
  • -t "||"：用竖线代替逗号作为列分隔符
  • -r "ENDROW"：自定义行终止符
  • 日期格式121：ISO8601标准格式（yyyy-mm-ddThh:mi:ss.mmm）

2.2 Excel格式特攻队：SSMS的魔法配方

在SSMS导出向导的"选择数据源"步骤：

  1. 在"列"映射界面，对日期列点击"转换"
  2. 选择"字符串"类型，格式字符串填yyyy-mm-dd hh:mi:ss
  3. 对长数字字段设置"文本"类型，预防科学计数法

2.3 JSON格式美颜术：FOR JSON的华丽转身

-- 生成带格式的JSON（SQL Server 2016+）
SELECT 
    OrderID,
    CustomerName = JSON_QUERY('"' + STRING_ESCAPE(CustomerName,'json') + '"'),
    DeliveryAddress = STRING_ESCAPE(DeliveryAddress,'json'),
    OrderDate = CONVERT(VARCHAR(23), OrderDate, 126)
FROM ExportDemo
FOR JSON PATH, ROOT('Orders')

输出示例：

{
  "Orders": [
    {
      "OrderID": 1,
      "CustomerName": "张,三",
      "DeliveryAddress": "北京市朝阳区,奥运村街道",
      "OrderDate": "2023-08-01T00:00:00"
    }
  ]
}

通过STRING_ESCAPE和日期格式转换，轻松解决JSON特殊字符转义问题。

三、技术方案选型指南

3.1 工具界的三国演义

工具 适用场景 处理速度 上手难度 格式控制精度
SSMS向导 小数据量即时导出 ★★☆☆☆ ★☆☆☆☆ ★★☆☆☆
BCP工具 大数据量定时任务 ★★★★★ ★★★☆☆ ★★★★☆
FOR JSON API接口数据准备 ★★★☆☆ ★★★★☆ ★★★★★

3.2 性能优化七武器

  1. 批量提交：BCP使用-b参数分批提交（建议5000-10000行/批）
  2. 并行导出：对不同表分区使用多个BCP进程
  3. 内存优化：在SSMS中设置-T 32767提高网络包大小
  4. 临时禁用索引：导出前禁用非聚集索引
  5. 格式预转换：在查询阶段完成格式处理
  6. 文件压缩：导出后立即进行ZIP压缩
  7. 错误重试机制：使用PowerScript实现自动重试

四、避坑指南——前辈们踩过的雷

4.1 编码三重奏

  • UTF-8 BOM：在BCP中使用-C 65001时，添加-N参数生成BOM头
  • ANSI陷阱：Windows系统默认ANSI编码，建议统一使用UTF-8
  • 特殊字符集：包含emoji表情时需使用UTF-16LE

4.2 日期时空穿越

/* 安全日期转换方案 */
SELECT 
    CONVERT(VARCHAR(10), OrderDate, 120) AS [Date], -- yyyy-mm-dd
    CONVERT(VARCHAR(19), OrderDate, 126) AS [ISO8601], -- yyyy-mm-ddThh:mi:ss
    CONVERT(VARCHAR(23), OrderDate, 127) AS [ISO8601-Z] -- 带时区
FROM ExportDemo

4.3 大数据量生存指南

当处理超过1GB的导出任务时：

  1. 使用OPENROWSET(BULK...)分段读取
  2. 在BCP中增加-a 16384提高网络包大小
  3. 禁用SSMS的"实时查询结果"

五、实战演练：从混乱到秩序

案例背景

某电商平台需要将10GB订单数据迁移到新系统，原始数据包含：

  • 用户评论中的表情符号和特殊字符
  • 包含逗号的地址信息
  • 精确到毫秒的支付时间

解决方案

# 组合拳脚本（PowerShell + BCP）
$query = @"
SELECT 
    OrderID,
    CustomerName = STRING_ESCAPE(CustomerName,'json'),
    CONVERT(VARCHAR(27), OrderDate, 126) + '.000' AS OrderDate,
    DeliveryAddress = REPLACE(STRING_ESCAPE(DeliveryAddress,'json'), ',', '，')
FROM ExportDemo
"@

bcp "$query" queryout "orders.json" -C 65001 -c -J -S DBServer -U admin -P ********
7z a -tzip orders.zip orders.json

效果检验

{
  "OrderID": 12345,
  "CustomerName": "王\u0026小美",  // Unicode转义
  "OrderDate": "2023-08-15T14:30:45.123000",
  "DeliveryAddress": "上海市浦东新区，世纪大道100号"  // 中文逗号替换
}

六、未来战场：智能化格式管理

随着SQL Server 2022的增强，我们可以期待：

  1. AI自动格式识别：根据目标系统智能调整格式
  2. 动态转义引擎：自动检测特殊字符模式
  3. 智能压缩传输：自动选择最优编码和压缩方式
  4. 区块链验签：导出文件自带数字签名