Saturday, March 12, 2005

Unicode problem for MySQL clients

After trying various MySQL client tools such as Navicat, sqlyog and MySQL Control Center, to my supprise, none of them supports UTF8 completely. Finally I tried phpMyAdmin and it displayed All chinese utf8 characters correctly, inserts are correctly done too.

Import data from Access2002 via MyODBC3.51

1. Texts in Access are store as unicode but turned into latin1 after import.
2. Migration from latin1 to utf8:
(a) Alter table xxx character set GBK; (The content saved in latin1 field is actually GBK encoded. --don't know why)
(b) Alter table xxx modify column1 varbinary(40);
or Alter table xxx modify column1 binary(40);
or Alter table xxx modify column1 blob;
(because no conversion is done when change from/to binary data)
(c) Alter table xxx modify column1 varchar(40) character set GBK; or else...
(d) Repeat step (b)&(c) with all other text columns (char, varchar, text)
(c) Alter table xxx convert to character set utf8; (We need to convert GBK to utf8)

This is very lengthy and boring. I hope there is some other smarter conversion from access to MySQL 4.1 out there.


Post a Comment

<< Home