CODEWITHSUNDEEP
mysqlutf-8
handros
handros

Reputation: 607

utf8 garbled when importing into mysql

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.

Upvotes: 35

Views: 48442

Answers (4)

Less
Less

Reputation: 3217

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:

  1. Set initial collation while creating the target database to utf8_unicode_ci

  2. Add SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; to the top of your sql file

  3. 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.

Upvotes: 78

AliSh
AliSh

Reputation: 10659

Use this command for import utf8 table to database :

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

Upvotes: 19

handros
handros

Reputation: 607

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

SET NAMES utf8;

Upvotes: 13

Greeso
Greeso

Reputation: 8269

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.

Upvotes: 1

Related Questions