如何将带有重音字符的 CSV 文件导入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6688138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I import a CSV file with accented characters into MySQL
提问by Barry Chapman
I have a file.csv with 300,000 rows. Many of the rows are the names of foreign cities with accented characters. When I try to do an import in mysql, I get warnings on the fields, and it truncates the field at the point of the special character.
我有一个包含 300,000 行的 file.csv。许多行是带有重音字符的外国城市的名称。当我尝试在 mysql 中进行导入时，我收到有关字段的警告，并且它会在特殊字符处截断该字段。
LOAD DATA LOCAL INFILE '/var/tmp/geoip/location.csv' INTO TABLE Geolocation2 FIELDS TERMINATED BY ',' enclosed by '"' LINES TERMINATED BY '\n' (id, country, region, city, postalCode, latitude, longitude, metrocode, areacode );
采纳答案by Dan Grossman
You need to set the connection, database, table and column encodings to the same character set as the data was saved in the CSV file.
您需要将连接、数据库、表和列编码设置为与保存在 CSV 文件中的数据相同的字符集。
回答by Naemoor
Open and SaveAs all your SQL Query and Data Files with UTF-8 encoding
使用 UTF-8 编码打开并另存为所有 SQL 查询和数据文件
This will solve BULK INSERT problems use option WITH (DATAFILETYPE = 'widenative')
这将解决 BULK INSERT 问题使用选项 WITH (DATAFILETYPE = 'widenative')
It will also solve INSERT INTO Problems whether the data is in the same file as the CREATE TABLE instruction or chained :r "X:\Path\InsertIntoMyTable.sql"
它还将解决 INSERT INTO 问题，无论数据与 CREATE TABLE 指令在同一个文件中还是链接 :r "X:\Path\InsertIntoMyTable.sql"
回答by Glenn Lawrence
回答by josdem
This sequence works to me.
这个序列对我有用。
- create database {$databasename} DEFAULT CHARACTER SET latin1;
- ALTER DATABASE {$databasename} DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci;
- charset latin1;
- load data infile '{$file.csv}' into table {$tablename} character set latin1 fields terminated by '|' enclosed by '"' lines terminated by '\n';
- 创建数据库 {$databasename} DEFAULT CHARACTER SET latin1;
- ALTER DATABASE {$databasename} DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci;
- 字符集 latin1;
- 将数据 infile '{$file.csv}' 加载到表 {$tablename} 字符集 latin1 字段中，以 '|' 结尾 由以 '\n' 结尾的 '"' 行括起来；
回答by webaware
Tell MySQL what the codepage of the source file is when you import it. e.g. to import a file with codepage Windows-1252, use MySQL codepage latin1 (which is the same thing) like this:
导入时告诉 MySQL 源文件的代码页是什么。例如，要导入代码页为 Windows-1252 的文件，请使用 MySQL 代码页 latin1（这是同一件事），如下所示：
LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE imported_table
CHARACTER SET 'latin1'
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
