Chinese Characters in mySQL Table

[StrangerInBeijing]

I'm not a mySQL pro, so excuse my lack of knowledged.

I create a table like this:

CREATE TABLE provinces (
id int unsigned not null auto_increment primary key,
country_id int not null,
name_en char(40),
name_zh char(40)
);

And insert data like this:

INSERT INTO provinces VALUES(NULL, 1, 'Anhui','安徽');
INSERT INTO provinces VALUES(NULL, 1, 'Beijing','北京');
INSERT INTO provinces VALUES(NULL, 1, 'Chongqing','重庆');

But when I Select data (i'm using the mysql console all the way) like this:

SELECT * FRom provinces

The chinese province names shows up as question marks (???)

I remember with MS Sql I set the collation type or something, but I dont know how to work with mySQL

[StrangerInBeijing]

oh, wait a minute..i just realized:

When I copy and past the sql i saved in a text document (and saved as UTF-8), into the mysql console, it allready show as question marks

Also, i see in phpmyadmin, one can set the collation type of the column. How do one do this with sql code (guess i just have to google that one)

[StrangerInBeijing]

OK, code for changing character set and collation is easy.

ALTER TABLE `provinces` CHANGE `name_zh` `name_zh` CHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL

Not sure which to set it to for chinese though.

Tried under several settings to enter data via phpMyAdmin Console, but still chinese show up as ??'s in result set.

[MaximilianMayrhofer]

I think i've seen this before, try using an N before the char as follows:

CREATE TABLE provinces (
id int unsigned not null auto_increment primary key,
country_id int not null,
name_en char(40),
name_zh Nchar(40)
);

[StrangerInBeijing]

yeah, that worked with sql server (actually putting an N before the variable that is unicode when you insert/update)

but i tried your way, the way above.....still no luck

[MaximilianMayrhofer]

Have you tried saving as UTF-16? And as long as you always put an N before the actual character on input, it will work fine, so why can't you do that?

[StrangerInBeijing]

Have you tried saving as UTF-16?

How do you do that in mysql?

[MaximilianMayrhofer]

You cannot store UTF-8 data in an SQL Server database. But UTF-8 is
just an encoding form of Unicode, and in SQL Server you store Unicode
data as UTF-16. How exactly, I am not 100% sure. But this article might help you.

[StrangerInBeijing]

mySql, not MS SQL .. i'm done with MS stuff

[amcgall]

I am definitely NOT a mySQL expert. Far from it.

I have a database which contains Traditional Chinese characters. I believe Big5.
I have exported the database structure and data. I wish to import it into a new host and mySQL database. When I start the import the import process errors out right at the point where it is importing data which contains Chinese characters.

I have a feeling mySQL export has messed up the Chinese characters. Can anyone help me? I am definitely a mySQL neophyte. Actually, I'm not afraid to admit that I am just plain dumb about mySQL.