Entering Chinese Characters

[J2R]

I am trying to set up a facility for storing Chinese names in a MySQL 5 database. I've read up as much as I can about setting column types, collation, etc., for the database, but the problem I'm now having is in using SQLYog to get the text in to the db for testing. I have an Excel spreadsheet with two columns, an English name and its Chinese equivalent (which displays perfectly in Excel). When I copy from one of these cells into SQLYog, what gets pasted in is just '???'. How can I get the editor window to display Chinese characters correctly? I don't imagine this is anything to do with db connection settings or the like, because the problem is occurring before I'm attempting to make a connection at all.

[peterlaursen]

“When I copy from one of these cells into SQLYog, what gets pasted in is just '???' .. that happens because Excel encodes as Windows Unicode (UTF16) – what SQLyog cannot handle internally.  

Internally SQLyog only recognizes ANSI.

You can store a unicode as a character set (ucs2, utf8) or a non-unicode character set ('big5' for instance') as you like.  

But SQLyog cannot handle unicode internally.  For the connection manager you will have to choose a chinese non-unicode character set (like 'big5).  The MySQL server will than transform when sending and recieving.

But copy-paste from Excel to SQLyog won't work.

We will release the first SQLyog (beta) with unicode support in a few days. that will make things easier.  Do you want an early beta for testing?  If so create a ticket and we will let you have one maybe even tonight or tomorrow.

a FAQ: http://webyog.com/faq/34_102_en.html

(but that we change soon!)

[J2R]

peterlaursen wrote on Mar 20 2007, 04:44 AM:

We will release the first SQLyog (beta) with unicode support in a few days. that will make things easier. Do you want an early beta for testing? If so create a ticket and we will let you have one maybe even tonight or tomorrow.

I'd be very interested, yes. How do I create a ticket?

In the meantime, to stop me tearing any more of my hair out, can you please tell me ANY means whereby I can enter some Chinese characters into a MySQL db via an INSERT in SQLYog and then view them again via a SELECT in SQLYog? I can create a new database with any required character set, collation, etc. I just need to see one 'round trip' working. I don't know if I actually have any means of entering Chinese characters from my UK keyboard, so I imagine the best bet would be to enter something as a hex value. I have tried to enter a Chinese word by means of the decimal Unicode values, as below, but the column ends up empty (or at least length returns 0 for it, which I assume means it's empty).

set names 'big5';

CREATE TABLE `test` (

`english_name` varchar(30) default NULL,

`chinese_name` varchar(30) character set big5 default NULL

) ENGINE=InnoDB DEFAULT CHARSET=big5;

insert into test(english_name,chinese_name) values (“Accrington”,char(33406,32527,38931));

select length(chinese_name) from test;

[peterlaursen]

Create a ticket from the link in 'about us' (top menu).

I have no experience with Chinese but from control panel .. regional settings you can change the locale.

that is the only option.  As a non-unicode program SQLyog needs the locale of the OS to be chinese is chinese shall be supported! I once have managed Thai and Arabic in SQLyog that way in combination with the Windows 'character map'  (start menu .. accessories .. system tool).  

hex-numbers of characters will not work from the GUI, but I think that they will work from a text file with LOAD DATA LOCAL INFILE (That is the same as 'CSV-import' in SQLyog).   (that is a bug with MySQL actually – 

there is no option to specify a charset for LOAD DATA – it will come with 5.0.38 only.

After 5.0.38 you should be able to “LOAD DATA LOCAL INFILE CHARSET BINARY … “)

Now if you create that ticket I'll araange that you get the next Unicode build for test.  Will be this week.  Maybe even tomorrow!

[peterlaursen]

Is it COMMUNITY (free) or ENTERPRISE version you are using?

[Author]

Posts