导入mysql时utf8出现乱码 [英] utf8 garbled when importing into mysql
问题描述
将UTF8编码的数据导入mysql对我来说不起作用. UTF8字符已损坏.例如,Nöthnagel显示为Nöthnagel
我创建了一个SQL转储文件来执行导入,该文件包含UTF-8编码的数据.例如:
INSERT INTO `users` VALUES(1, 'Fred','Nöthnagel');
文件中表示ö的字节序列是c3 b6,我认为是正确的,因为它在vim和设置了以下环境变量的bash shell中正确显示:
$ env | grep -i utf
LANG=en_US.UTF-8
XTERM_LOCALE=en_US.UTF-8
mysql数据库的创建如下:
mysql> CREATE DATABASE mydb CHARACTER SET utf8;
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;
我正在导入转储文件,如下所示:
mysql -u root -psecret mydb < mydump.sql
请告诉我以上内容中缺少的内容.
我认为它也可能与排序规则有关,但我不确定.就我而言,确实如此,因为我必须支持西里尔字母.
试试这个,为我工作:
-
在创建目标数据库时将初始排序规则设置为
utf8_general_ci
-
将
SET NAMES 'utf8' COLLATE 'utf8_general_ci';
添加到sql文件的顶部 -
运行
mysql -u root -p --default-character-set=utf8 yourDB < yourSQLfile.sql
另一件事,为了从数据库中正确获取UTF-8数据,还必须修改连接字符串.例如:
mysql.url=jdbc:mysql://localhost:3306/nbs?useJvmCharsetConverters=false&useDynamicCharsetInfo=false&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&useEncoding=true
另外,看看我的问题是什么. >
Importing UTF8-encoded data into mysql is not working for me. UTF8 characters are corrupted. For example Nöthnagel is displayed as Nöthnagel
I have created a sql dump file to do the importing which contains UTF-8 encoded data. For example:
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:
$ env | grep -i utf
LANG=en_US.UTF-8
XTERM_LOCALE=en_US.UTF-8
The mysql db was created as follows:
mysql> CREATE DATABASE mydb CHARACTER SET utf8;
The mysql table was created so:
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.
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_ci
Add
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
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:
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 problem was.
这篇关于导入mysql时utf8出现乱码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!