Chinese Characters in mySQL Table

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

I create a table like this:

Code:

---

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:

Code:

---

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:

Code:

---

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

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)

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

Code:

---

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.

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

Code:

---

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)
);

---

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

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?

Quote:

---

Originally Posted by MaximilianMayrhofer

Have you tried saving as UTF-16?

---

How do you do that in mysql?

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.

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

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.