utf8 garbled when importing into mysql

Question

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.

that's a good summary, but how are you doing the final display that gives NÃ¶thnagel — Paul Dixon, Nov 05 '12 at 14:47
Have tried to use SET NAMES statement ? http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html — Andrea, Nov 05 '12 at 14:48
@PaulDixon The original data is on a django website that uses sqlite. The website is now running mysql. Only the data and the django config have changed, so it is easy to compare them — handros, Nov 05 '12 at 15:01
@PierredeLESPINAY The encoded data for ö in the file is c3 b6. I have checked other sources to confirm that this is indeed utf8 — handros, Nov 05 '12 at 15:04
Thank you for your question here! You saved me a lot of time! — Eugene Kaurov, Jun 19 '22 at 21:07

score 76 · Accepted Answer · edited Sep 29 '22 at 17:18

76

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_unicode_ci
Add SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; to the top of your sql file
Run 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.

edited Sep 29 '22 at 17:18

ᴍᴇʜᴏᴠ

4,804
4
44
57

answered Nov 05 '12 at 14:54

Less

3,047
3
35
46

default character set worked even for export. thx #mysql db_name -p -u --default-character-set=utf8 < query.sql > result-utf8.csv – jir Oct 13 '17 at 08:11
Your point number 3 was enough on my setup. I will mention here a few things I noticed. The default sql dump read as ISO-8859-1 in Windows Notepad but as UTF-8 in Sublime Text. I tried 'Save with encoding: UTF-8' from Sublime and got a correct readout in Notepad, but this broke the import into MySQL using the method in this answer. However I could still ```SOURCE``` the file, but the result had the wrong encoding anyway which left me very confused. The point being that the issue lies solely in the database import, and that ```SOURCE``` doesn't care. – glaux May 16 '18 at 09:26
point #2 was enough! SET NAMES 'utf8' COLLATE 'utf8_general_ci'; Thank you very much and God bless you! German is a special language. – Eugene Kaurov Jun 19 '22 at 21:05

score 18 · Answer 2 · answered Feb 02 '16 at 15:30

18

Use this command for import utf8 table to database :

mysql -u USERNAME  -pPASSWORD --default_character_set utf8  DATABASE < file.sql

answered Feb 02 '16 at 15:30

AliSh

10,085
5
44
76

score 13 · Answer 3 · answered Nov 05 '12 at 15:18

13

The problem was solved by adding this at the top of the sql file:

SET NAMES utf8;

answered Nov 05 '12 at 15:18

handros

597
1
5
14

Both this and `--default_character_set utf8` worked, but I think I'll stick with this one. When I use `mysqldump` instead of `phpmyadmin` to export the database, I see this setting in the dump. However, phpmyadmin doesn't have this in its dump. – akinuri Mar 06 '23 at 15:07

score 1 · Answer 4 · answered Nov 05 '12 at 14:57

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.

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 garbled when importing into mysql

4 Answers4

Linked