将 utf-8 编码的文本加载到 MySQL 表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4957900/
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
Loading utf-8 encoded text into MySQL table
提问by Hossein
I have a large CSV file that I am going to load it into a MySQL table. However, these data are encoded into utf-8 format, because they include some non-english characters. I have already set the character set of the corresponding column in the table to utf-8. But when I load my file. the non-english characters turn into weird characters(when I do a select on my table rows). Do I need to encode my data before I load the into the table? if yes how Can I do this. I am using Python to load the data and using LOAD DATA LOCAL INFILE command. thanks
我有一个很大的 CSV 文件，我要将它加载到 MySQL 表中。但是，这些数据被编码为 utf-8 格式，因为它们包含一些非英文字符。我已经将表中对应列的字符集设置为utf-8。但是当我加载我的文件时。非英文字符变成奇怪的字符（当我在我的表格行上做一个选择时）。在将数据加载到表中之前是否需要对数据进行编码？如果是的话，我该怎么做。我正在使用 Python 加载数据并使用 LOAD DATA LOCAL INFILE 命令。谢谢
采纳答案by dweeves
as said in http://dev.mysql.com/doc/refman/5.1/en/load-data.html, you can specify the charset used by your CSV file with the "CHARACTER SET" optional parameter of LOAD DATA LOCAL INFILE
如http://dev.mysql.com/doc/refman/5.1/en/load-data.html所述，您可以使用 LOAD DATA LOCAL INFILE 的“CHARACTER SET”可选参数指定 CSV 文件使用的字符集
回答by JMHeap
Try
尝试
LOAD DATA INFILE 'file'
IGNORE INTO TABLE table
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
回答by BuiXuanThanh
Do not need encode your characters in the file, but you need to make sure that your file is encoding at UTF-8 before load this file to database.
不需要在文件中编码您的字符，但在将此文件加载到数据库之前，您需要确保您的文件以 UTF-8 编码。
回答by simon
You should send
你应该发送
init_command = 'SET NAMES UTF8'
use_unicode = True
charset = 'utf8'
when doing MySQLdb.connect() e.g.
当做 MySQLdb.connect() 例如
dbconfig = {}
dbconfig['host'] = 'localhost'
dbconfig['user'] = ''
dbconfig['passwd'] = ''
dbconfig['db'] = ''
dbconfig['init_command'] = 'SET NAMES UTF8'
dbconfig['use_unicode'] = True
dbconfig['charset'] = 'utf8'
conn = MySQLdb.connect(**dbconfig)
edit: ah, sorry, I see you've added that you're using "LOAD DATA LOCAL INFILE" -- this wasn't clear from your initial question :)
编辑：啊，对不起，我看到你补充说你正在使用“加载数据本地文件”——这从你最初的问题中不清楚:)
回答by Hasitha Nanayakkara
Try something like,
尝试类似的东西，
LOAD DATA LOCAL INFILE "file" INTO TABLE message_history CHARACTER SET UTF8 COLUMNS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"';
LOAD DATA LOCAL INFILE "file" INTO TABLE message_history CHARACTER SET UTF8 COLUMNS TERMINATED BY '|' 可选地由 '"' ESCAPED BY '"' 括起来；
Original Structure,
原始结构，
