如何将utf16le编码的5亿条多语言CSV导入MySQL 5.7 Aurora

即时报错的根因与修复 #

• charmap codec can't decode报错根因：Workbench的可视化导入向导默认识别的utf16编码是带BOM的大端格式，而源文件是无BOM的utf16le，向导无法自动识别编码格式，并非文件本身损坏。
• could not determine delimiter报错根因：原PowerShell生成测试文件的命令存在硬伤，直接把CSV文件的结构和编码破坏了，并非Workbench识别不了逗号分隔符：
  • 原命令Get-Content .\data.csv | select -First 10 | Out-File ./test.csv未显式指定编码，PowerShell 5.1的Out-File默认使用系统ANSI编码写入，把原utf16le的内容转成了本地编码，出现乱码后自然识别不了分隔符
  • 管道逐行读取的过程会把CSV字段内嵌的换行、转义逗号拆成独立行，破坏了原CSV的标准结构
  • 正确的测试文件生成命令（匹配原文件编码，不破坏CSV结构）：

    Get-Content .\data.csv -Encoding Unicode -TotalCount 10 | Out-File ./test.csv -Encoding Unicode
    

    其中PowerShell参数里的Unicode对应无BOM的utf16le格式，和源文件编码完全匹配。

快速生成正式表结构的正确流程 #

不要依赖Workbench可视化导入向导猜字段类型，对多语言大样本文件来说准确率极低，按以下流程操作靠谱很多：

• 先创建临时预览表，所有字段临时设为TEXT类型，表字符集指定为utf16，兼容所有语言的字符存储
• 用MySQL原生LOAD DATA LOCAL INFILE加载刚才生成的10行测试文件，导入语句里显式指定编码和CSV格式规则：

  LOAD DATA LOCAL INFILE './test.csv'
  INTO TABLE temp_csv_preview
  CHARACTER SET utf16
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 ROWS;
  

• 从临时预览表中统计每个字段的最大长度、值类型（数值/日期/文本）、多语言字符分布，直接生成正式的生产表结构，比向导自动猜的结果准确率高很多。

5亿条级数据从S3加载的最佳实践 #

• 编码适配：正式从S3加载数据时，导入语句必须显式指定CHARACTER SET utf16，不要依赖实例默认的utf8mb3字符集，否则多语言字符会出现乱码；MySQL 5.7本身对utf16le编码的文件支持良好，只要显式声明编码，即可解决无BOM文件的解码问题。
• 加载性能优化：
  • 提前在S3上将原大文件切分为1-2GB大小的独立分片，并行执行加载任务，避免单文件过大导致加载超时，整体加载速度可提升数倍
  • 正式表加载前先删除所有二级索引、禁用外键校验，等全部数据加载完成后再重建索引、开启外键，比边加载边建索引的效率高10倍以上
  • 加载阶段临时将innodb_flush_log_at_trx_commit参数设为2，加载完成后再恢复为默认值1，可大幅减少磁盘IO等待
• 分隔符类报错规避：只要不通过逐行管道工具修改原文件的编码和结构，保持原CSV的双引号包裹字段的标准格式，即使字段内嵌逗号、换行，只要在导入语句中指定ENCLOSED BY '"'规则，就不会出现分隔符识别错误。

内容的提问来源于stack exchange，提问作者user15793580