MySQL 导入mysql时utf8乱码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13234433/
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
utf8 garbled when importing into mysql
提问by handros
Importing UTF8-encoded data into mysql is not working for me. UTF8 characters are corrupted. For example N?thnagelis displayed as N??thnagel
将 UTF8 编码的数据导入 mysql 对我不起作用。UTF8 字符已损坏。例如N?thnagel显示为N??thnagel
I have created a sql dump file to do the importing which contains UTF-8 encoded data. For example:
我创建了一个 sql 转储文件来执行包含 UTF-8 编码数据的导入。例如：
INSERT INTO `users` VALUES(1, 'Fred','N?thnagel');
The sequence of bytes representing ?in the file is c3 b6 which I believe is correct, as it displays correctly in vim and in my bash shell which has these environment variables set:
表示的字节序列？文件中的 c3 b6 我认为是正确的，因为它在 vim 和我的 bash shell 中正确显示，其中设置了这些环境变量：
$ env | grep -i utf
LANG=en_US.UTF-8
XTERM_LOCALE=en_US.UTF-8
The mysql db was created as follows:
mysql 数据库创建如下：
mysql> CREATE DATABASE mydb CHARACTER SET utf8;
The mysql table was created so:
mysql 表是这样创建的：
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
I am importing the dump file like so:
我正在像这样导入转储文件：
mysql -u root -psecret mydb < mydump.sql
Please tell me what is missing from the above.
请告诉我上面缺少什么。
回答by Less
I think it might have something to do with collation as well, but I'm not sure. In my case it certainly did, since I had to support cyrillic.
Try this, worked for me:
我认为这也可能与整理有关，但我不确定。就我而言，确实如此，因为我必须支持西里尔字母。
试试这个，对我有用：
Set initial collation while creating the target database to
utf8_general_ciAdd
SET NAMES 'utf8' COLLATE 'utf8_general_ci';to the top of your sql fileRun
mysql -u root -p --default-character-set=utf8 yourDB < yourSQLfile.sql
创建目标数据库时将初始排序规则设置为
utf8_general_ci添加
SET NAMES 'utf8' COLLATE 'utf8_general_ci';到 sql 文件的顶部跑
mysql -u root -p --default-character-set=utf8 yourDB < yourSQLfile.sql
One more thing, in order to properly get the UTF-8 data form your database, you'll have to modify your connection string as well. For example:
还有一件事，为了从数据库中正确获取 UTF-8 数据，您还必须修改连接字符串。例如：
mysql.url=jdbc:mysql://localhost:3306/nbs?useJvmCharsetConverters=false&useDynamicCharsetInfo=false&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&useEncoding=true
mysql.url=jdbc:mysql://localhost:3306/nbs?useJvmCharsetConverters=false&useDynamicCharsetInfo=false&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&useEncoding=true
Additionally, take a look at what my problemwas.
另外，看看我的问题是什么。
回答by handros
The problem was solved by adding this at the top of the sql file:
通过在 sql 文件的顶部添加以下内容解决了该问题：
SET NAMES utf8;
回答by AliSh
Use this command for import utf8 table to database :
使用此命令将 utf8 表导入数据库：
mysql -u USERNAME -pPASSWORD --default_character_set utf8 DATABASE < file.sql
回答by Greeso
I had a similar problem. There are a number of variables that should be UTF8, not only the database, those include the client, the connection, ther server ...etc.
我有一个类似的问题。有许多变量应该是 UTF8，不仅是数据库，还包括客户端、连接、服务器等。
The solution to your problem is described in this article. The described solution is portable, so it does not only work for utf8, but for all other character sets. You may need to modify it to fit your needs.
本文描述了您的问题的解决方案。所描述的解决方案是可移植的，因此它不仅适用于 utf8，还适用于所有其他字符集。您可能需要对其进行修改以满足您的需要。
