从S3加载含特殊字符的CSV到MySQL时的列错位问题解决

问题场景 #

S3存储的CSV结构如下：

"id","created_at","subject","description","priority","status","recipient",

其中某条记录的description列包含常规文本、JSON代码、curl命令及转义引号（如\"string\"、\"subId\":）等特殊字符。使用以下LOAD DATA FROM S3命令导入时，多数记录正常，但这条特殊记录会出现列错位（例如description内容被错误导入priority列）：

LOAD DATA FROM S3 's3://{s3_bucket_name}/{s3_key}'
                    REPLACE
                    INTO TABLE {schema_name}.stg_tickets
                    CHARACTER SET utf8mb4
                    FIELDS 
                        TERMINATED BY ','
                        ENCLOSED BY '"'
                    IGNORE 1 LINES
                    (id, url, @created_at, @updated_at, @external_id, @type, subject, 
                    description, priority, status, recipient, requester_id, 
                    submitter_id, assignee_id, @organization_id, @group_id, 
                    collaborator_ids, follower_ids, @forum_topic_id, @problem_id, 
                    @has_incidents)

限制：无法预处理S3文件，只能修改MySQL命令，且通过Python执行查询。

解决方案 #

方法1：添加转义字符识别参数 #

MySQL的LOAD DATA支持ESCAPED BY参数，用于解析CSV中的转义字符。由于你的CSV用\"表示转义引号，只需在FIELDS配置中添加该参数，让MySQL正确识别被转义的引号，避免提前终止description列的读取。

修改后的命令：

LOAD DATA FROM S3 's3://{s3_bucket_name}/{s3_key}'
                    REPLACE
                    INTO TABLE {schema_name}.stg_tickets
                    CHARACTER SET utf8mb4
                    FIELDS 
                        TERMINATED BY ','
                        ENCLOSED BY '"'
                        ESCAPED BY '\\'  -- 新增：识别转义的反斜杠
                    IGNORE 1 LINES
                    (id, url, @created_at, @updated_at, @external_id, @type, subject, 
                    description, priority, status, recipient, requester_id, 
                    submitter_id, assignee_id, @organization_id, @group_id, 
                    collaborator_ids, follower_ids, @forum_topic_id, @problem_id, 
                    @has_incidents)

注意：在Python中执行时，SQL字符串里的反斜杠需要双重转义，要写成ESCAPED BY '\\\\'，或者使用Python原始字符串（如r"ESCAPED BY '\\'"），防止被Python解析为转义字符。

方法2：手动解析整行数据（兜底方案） #

如果转义参数不生效，可以通过用户变量读取整行，再用字符串函数手动拆分列，确保description被完整捕获。核心思路是通过固定的列位置定位，跳过description内部的分隔符。

示例命令：

LOAD DATA FROM S3 's3://{s3_bucket_name}/{s3_key}'
                    REPLACE
                    INTO TABLE {schema_name}.stg_tickets
                    CHARACTER SET utf8mb4
                    FIELDS TERMINATED BY '\n'  -- 按行读取整行数据
                    IGNORE 1 LINES
                    (@raw_line)
                    SET 
                        id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', 1), '"', -1),
                        url = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', 2), '","', -1),
                        @created_at = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', 3), '","', -1),
                        @updated_at = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', 4), '","', -1),
                        @external_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', 5), '","', -1),
                        @type = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', 6), '","', -1),
                        subject = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', 7), '","', -1),
                        -- 提取description：从第7个分隔符后到倒数第5个分隔符前的内容
                        description = TRIM(BOTH '"' FROM SUBSTRING(
                            @raw_line,
                            -- 定位到subject之后的起始位置
                            LOCATE('","', @raw_line, LOCATE('","', @raw_line, LOCATE('","', @raw_line, LOCATE('","', @raw_line, LOCATE('","', @raw_line, LOCATE('","', @raw_line)+1)+1)+1)+1)+1)+2,
                            -- 计算description的长度：总行长减去末尾5列的长度
                            LENGTH(@raw_line) - LOCATE('","', REVERSE(@raw_line), LOCATE('","', REVERSE(@raw_line), LOCATE('","', REVERSE(@raw_line), LOCATE('","', REVERSE(@raw_line), LOCATE('","', REVERSE(@raw_line))+1)+1)+1)) - 2
                        )),
                        priority = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -5), '","', 1),
                        status = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -4), '","', 1),
                        recipient = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -3), '","', 1),
                        requester_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -2), '","', 1),
                        submitter_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -1), '","', 1),
                        assignee_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -6), '","', 1),
                        @organization_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -7), '","', 1),
                        @group_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -8), '","', 1),
                        collaborator_ids = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -9), '","', 1),
                        follower_ids = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -10), '","', 1),
                        @forum_topic_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -11), '","', 1),
                        @problem_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -12), '","', 1),
                        @has_incidents = SUBSTRING_INDEX(SUBSTRING_INDEX(@raw_line, '","', -13), '","', 1)

这种方法适合复杂场景，确保不管description内部有多少分隔符，都能正确提取。

方法3：使用LOCAL INFILE（备选） #

如果MySQL实例允许LOCAL INFILE，可以先通过Python将S3文件下载到本地，再用LOAD DATA LOCAL INFILE导入，该方式对转义字符的处理更稳定。需要满足：

• MySQL配置中local_infile=ON
• Python的MySQL客户端开启LOCAL支持（如pymysql连接时设置local_infile=True）

注意：此方法需要下载文件，若严格禁止预处理文件则不适用。

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