csv 文件导入 MySQL 遇到的转义字符问题
复现:
导入 SQL :
CREATE TABLE IF NOT EXISTS `test` (
`ID` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
`UTF8String` VARCHAR(20) 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
直接报错:
invalid utf8 character string '测试'
解决:
加入 ESCAPED BY '' ,不设置转义字符。
CREATE TABLE IF NOT EXISTS `test` (
`ID` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
`textStringUTF8` VARCHAR(20) 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';
————————————————
版权声明:本文为CSDN博主「qwerty26256」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qwerty26256/article/details/53141272