使用LOAD DATA导入CSV至SQL时触发1083错误，寻求解决方案

问题重现 #

执行以下SQL语句导入CSV时触发错误：

LOAD DATA LOW_PRIORITY LOCAL INFILE '/home/worker.csv'
IGNORE INTO TABLE `TEST_db`.`Employee_Info`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\'' ESCAPED BY '\''
LINES TERMINATED BY '\n'
IGNORE 1 LINES (`date`, `Name`, `Gender`, `Mobileno`, `Location`);

错误提示：

1083 (42000): Field separator argument is not what is expected; check the manual
Completed execution in 0.22 minutes

CSV文件格式示例：

'2022-10-31','X','Female','91XXXXX','Argentina'
'2022-10-31','Y','Male','71XXXXXX','India'
'2022-10-31','Z','Female','81XXXXXX','UK'

错误原因 #

语句中ESCAPED BY '\''与OPTIONALLY ENCLOSED BY '\''的设置冲突，导致MySQL无法正确解析字段分隔相关参数。当字段使用单引号作为包裹符时，标准CSV的转义规则是用两个单引号表示字段内的单个单引号，而非用单个单引号作为转义符。

解决方案 #

根据你的CSV格式，有两种修正方式：

方式1：删除多余的ESCAPED BY子句 #

如果你的CSV中没有需要转义的单引号，直接去掉ESCAPED BY '\''即可：

LOAD DATA LOW_PRIORITY LOCAL INFILE '/home/worker.csv'
IGNORE INTO TABLE `TEST_db`.`Employee_Info`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '\n'
IGNORE 1 LINES (`date`, `Name`, `Gender`, `Mobileno`, `Location`);

方式2：设置正确的转义符 #

如果后续CSV可能出现字段内包含单引号的情况，将转义符设置为两个单引号：

LOAD DATA LOW_PRIORITY LOCAL INFILE '/home/worker.csv'
IGNORE INTO TABLE `TEST_db`.`Employee_Info`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\'' ESCAPED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 LINES (`date`, `Name`, `Gender`, `Mobileno`, `Location`);

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