Why do Chinese characters display abnormal in MySQL and Perl?

Question

I use the Perl ORM Rose::DB::Object and the MySQL table/column collation utf8_general_ci. Code for saving data:

Motorcycle->new(
    type  => $self->param('type'),
    brand => $self->param('brand'),
    color => $self->param('color')
)->save;

Code for retrieving data in Mojolicious (the code is equivalent to utf8::decode):

<td><%= Mojo::ByteStream->new($cycle->type)->decode('utf-8') %></td>
<td><%= Mojo::ByteStream->new($cycle->brand)->decode('utf-8') %></td>
<td><%= Mojo::ByteStream->new($cycle->color)->decode('utf-8') %></td>

Chinese characters look fine in my application, but they seem strange in phpMyAdmin. If I save the characters in phpMyAdmin, they'll be fine in phpMyAdmin but look strange in my application.

I have tested it in SQLite and with the Firefox SQLite add-on, and the characters look fine in both the Firefox SQLite add-on and my application.

I think this is a server issue. If I fuse Perl with PHP, it can become a disaster.

Thanks for help.

---

RESOLVED as below:

__PACKAGE__->use_private_registry;
__PACKAGE__->default_connect_options( mysql_enable_utf8 => 1 );
__PACKAGE__->register_db(
driver   => 'mysql',
database => 'test',
host     => 'localhost',
username => 'root',
password => '',

Its important to add this line in:

__PACKAGE__->default_connect_options( mysql_enable_utf8 => 1 );

No decode needed when reading back the data.

Answer 1

Without seeing your MySQL connection code, it's hard to be certain, but most likely, you need to tell MySQL that your connection is going to use UTF-8 - otherwise, it interprets your bytes as some other character set, which would explain why phpMyAdmin isn't showing you the expected result.

For PHP with the default mysql library, that's what the mysql_set_charset is for (assuming you're using plain old mysql_query); for mysqli, you have the equivalent mysqli_set_charset.

If you use some other library to access MySQL, you'll have to look at its documentation for something similar. The same goes for perl; I don't know anything about that language, so I can't give you any specifics.

Answer 2

In Perl code you need to be sure that you Encode::decode() input so your data are in perl's internal character representation, work with your data, then Encode::encode() your output before writing to the database. You will probably also want to do:

$dbh->do('SET NAMES "utf8"');

after you connect to the database so that the MySQL client library and server agree on character set.

see: http://perldoc.perl.org/perlunitut.html and http://perldoc.perl.org/perlunicode.html