How to view mysql data in utf-8?


#1

Hi,

I dump all my database data from other site into dreamhost mysql, and with charset like
’) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

in create table sql.

Then I use mysql client ‘Navicat’ to browse the data. I found that the data I saw is in mess, not in utf-8 display even I set the connection charset to utf-8.

But it’s ok to view utf-8 data in my original mysql database.
Is that because of the mysql setting in dreamhost ? Or how can I view utf-8 data by client?

Thanks.

Gary


#2

What version of MySQL is on that other host you dump database from? Are imported data readable in mysqladmin? Are data readable in your web application (cms, forum, blog or whatever)?

You probably experiencing troubles with moving from mysql ver 4.0 to ver 4.1. If that is case you have to convert all varchar/text fields to utf-8, and to avoid charset conversion (your data are probably already in utf8 even if charset was set to latin1), you have to convert it to blob first, and afterwards to their original type, but now with utf8 encoding.

There is another issue with php, mysql 4.1 and utf8. After connecting to database from your web application, you must execute query “SET NAMES utf-8” if you want to retrieve correct data from db, otherwise some charset conversion will occur and you will get garbage on your pages.


#3

[quote]What version of MySQL is on that other host you dump database from?

[/quote]

4.1.13

[quote]Are imported data readable in mysqladmin?

[/quote]

no

[quote]Are data readable in your web application (cms, forum, blog or whatever)?

[/quote]

yes

[quote]You probably experiencing troubles with moving from mysql ver 4.0 to ver 4.1. If that is case you have to convert all varchar/text fields to utf-8, and to avoid charset conversion (your data are probably already in utf8 even if charset was set to latin1), you have to convert it to blob first, and afterwards to their original type, but now with utf8 encoding.

[/quote]

It seems that I’m NOT moving from ver 4.0 to v4.1, right?

[quote]There is another issue with php, mysql 4.1 and utf8. After connecting to database from your web application, you must execute query “SET NAMES utf-8” if you want to retrieve correct data from db, otherwise some charset conversion will occur and you will get garbage on your pages.

[/quote]

I’m using rails.

Any other possibility? Thanks a lot if you think of any.