csv 檔案導入 MySQL 遇到的轉義字元問題

複現：

導入 SQL ：

CREATE TABLE IF NOT EXISTS `test` (
`ID` INT() UNSIGNED NOT NULL AUTO_INCREMENT,
`UTF8String` VARCHAR() CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NULL,
PRIMARY KEY(`ID`),
UNIQUE KEY `ID_UNIQUE` (`ID`));

LOAD DATA LOCAL INFILE 'C:\\10.csv'
INTO TABLE `test`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';
           

10.csv 檔案問題資料行：

ID  UTF8String
1104    測試\　　111
           

直接報錯：

解決：

加入

ESCAPED BY ''

，不設定轉義字元。

CREATE TABLE IF NOT EXISTS `test` (
`ID` INT() UNSIGNED NOT NULL AUTO_INCREMENT,
`textStringUTF8` VARCHAR() CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NULL,
PRIMARY KEY(`ID`),
UNIQUE KEY `ID_UNIQUE` (`ID`));

LOAD DATA LOCAL INFILE 'C:\\10.csv'
INTO TABLE `test`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY '\t'
ESCAPED BY ''
LINES TERMINATED BY '\n';