Character Encoding Unicode/utf-8 Problem

[peterlaursen]

Please tell:

1) It it Vietnamese?

2) MySQL version?

3) If you ever tried version 5.x could you read it correctly with this one?

4) What program would you normally use to view the data?

5) Please connect to the database and execute “SHOW VARIABLES LIKE '%character%';” and paste the result here

6) Please create a small table with such characters, export(dump) it using utf8 encoding (the SQLyog 'export' does), zip and attach here.

“because they are encoded somehow”.  “SHOW VARIABLES LIKE '%character%';” will tell how it is encoded in the database.

[Bill Nguyen]

1) It it Vietnamese?

Yes, it's Vietnamese

2) MySQL version?

Server version: 5.0.27-standard-log

3) If you ever tried version 5.x could you read it correctly with this one?

I got same problem with 5.x

4) What program would you normally use to view the data?

SQLyog and phpMyAdmin

5) Please connect to the database and execute “SHOW VARIABLES LIKE '%character%';” and paste the result here

character set client utf8

(Global value) latin1

character set connection utf8

(Global value) latin1

character set database latin1

character set filesystem binary

character set results utf8

(Global value) latin1

character set server latin1

character set system utf8

character sets dir /usr/share/mysql/charsets/

collation connection utf8_unicode_ci

(Global value) latin1_swedish_ci

collation database latin1_swedish_ci

collation server latin1_swedish_ci

6) Please create a small table with such characters, export(dump) it using utf8 encoding (the SQLyog 'export' does), zip and attach here.

jos_content_dump.zip

Thanks

Bill

[peterlaursen]

please try open the dump in Notepad yourself.

all strings are stored in BLOB fields.  BLOB is a binary filed type, for strings you should use (var)char or text types.

Also this: ” ..values('4','Lấp lánh há»â€œn thÆ¡ Viá»â€¡t trên sân ga Tokyo chiá»ï¿½u cuá»â€˜i nÄÆ’m','','ký —”  clearly tells that data are NOT stored as Vietnamese in the database.  Looks like it is stored with som web-encoding.  I don't know exactly what it is, because I do not know Vietnamese, but this is NOT unicode!

There may be thousand ways to implement 'client side encoding'.  We do not support this.  SQLyog displays data as they are stored in the database.

[DonQuichote]

peterlaursen wrote on Jun 8 2007, 09:48 AM:

There may be thousand ways to implement 'client side encoding'. We do not support this. SQLyog displays data as they are stored in the database.

How did the texts get in the database? If it is from an self-written application (a PHP page, for example), make sure it sends a “SET NAMES utf8” (or whatever other encoding you work with) command directly after connecting. If you do not do this, MySQL defaults to latin1, even if you set a different default in the config file of the MySQL server.

Hope this helps.

[peterlaursen]

@don

he uses BLOB fields for storing of strings.  SET NAMES has no effect on binary data (like ((var)binary and BLO:cool:.

He is storing some web based encoding as binary data, I believe!  His has a client program of some kind that does the encoding/decoding.

This is not MySQL unicode and we do not support it – whatever it is!

[Bill Nguyen]

peterlaursen wrote on Jun 8 2007, 11:17 AM:

@don

He is storing some web based encoding as binary data, I believe!  His has a client program of some kind that does the encoding/decoding.

Peter;

The “binary” part is probably true for the article body. However, for article titles such as Nhà ăn lúc chiều tối và má»™t hồ bÆ¡i trong mÆ°a, are they also in binary?

You're correct in “web-based encoding” since we use an HTML editor to upload and maintain articles.

If I can send text directly (via sql) using SQLyog, what charset do I need to use?

Thanks

Bill

[peterlaursen]

OK .. I missed that the 'title' filed was a varchar.  As long as you only save single-byte characters that does not make much difference.

Try to create a table where every column (or the table itself) is UTF8. Ensure tht there are varchar or text columsn(s) in it.  From SQLyog simply enter Vietnamese characters from a Vietnamese keyboard.  Now try dump that table and you will see the Vietnamese characters directly displayed in the dump file (in this situation also the file itself will be encoded with utf8).

It may be problematic to change!  Probably quite a a lot a changes are required in the web front-end if data stored as Unicode in MySQL shall be displayed correctly as it seems that this web-frontend uses quite another concept!  But give it a try (and ensure that also uft8 encoding is specified in META tags for the web pages)

[Author]

Posts