MySQL导入CSV后查询数据显示异常，如何解决？

问题场景 #

使用以下SQL导入CSV文件：

LOAD DATA INFILE 'F:\\r-analysis\\202004-divvy-tripdata.csv'
INTO TABLE 2020_04_divvy_tripdata
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

执行结果显示导入成功：

Query OK, 199953 rows affected (2.85 sec)
Records: 199953  Deleted: 0  Skipped: 0  Warnings: 0

但查询时数据显示异常：

1. 执行select member_casual from 2020_04_divvy_tripdata limit 2;，member_casual字段显示为竖线：

+---------------+
| member_casual |
+---------------+
       |
       |
+---------------+
2 rows in set (0.00 sec)

2. 执行select ride_id, rideable_type, member_casual from 2020_04_divvy_tripdata limit 1;，字段内容错位，ride_id前出现竖线：

+------------------+---------------+---------------+
| ride_id          | rideable_type | member_casual |
+------------------+---------------+---------------+
       |D35674B983 | docked_bike   | member
+------------------+---------------+---------------+
1 row in set (0.00 sec)

CSV文件示例：

ride_id,rideable_type,started_at,ended_at,start_station_name,start_station_id,end_station_name,end_station_id,start_lat,start_lng,end_lat,end_lng,member_casual
02668AD35674B983,docked_bike,2020-05-27 10:52,2020-05-27 10:49,Franklin St & Jackson Blvd,36,Wabash Ave & Grand Ave,199,41.8777,-87.6353,41.8915,-87.6268,member
7A50CCAF1EDDB28F,docked_bike,2020-05-25 10:11,2020-05-25 11:40,Clark St & Wrightwood Ave,340,Clark St & Leland Ave,326,41.9295,-87.6431,41.9671,-87.6674,casual
2FFCDFDB91FE9A52,docked_bike,2020-05-02 14:03,2020-05-02 15:21,Kedzie Ave & Milwaukee Ave,260,Kedzie Ave & Milwaukee Ave,260,41.9296,-87.7079,41.9296,-87.7079,casual

问题原因 #

CSV文件是Windows系统生成的，行分隔符为\r\n（回车+换行），但导入语句中指定的是\n（Unix/Linux换行符），导致每行末尾的\r被保留到最后一个字段中。\r是回车控制符，会让查询输出时光标回到行首，从而造成内容错位、字段被覆盖的视觉异常。

解决方法 #

1. 修改导入语句，适配Windows换行符，同时添加可选的引号包裹规则（避免字段含逗号时拆分错误，提升兼容性）：

LOAD DATA INFILE 'F:\\r-analysis\\202004-divvy-tripdata.csv'
INTO TABLE 2020_04_divvy_tripdata
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

2. 清空表中已导入的异常数据（可选，确保数据干净）：

TRUNCATE TABLE 2020_04_divvy_tripdata;

3. 执行修改后的导入语句重新导入数据。

验证方法 #

重新导入后执行查询：

select ride_id, rideable_type, member_casual from 2020_04_divvy_tripdata limit 2;

正常情况下会显示对齐正确的字段内容，无错位或竖线异常。

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