If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

I have a MySQL database which runs on a remote server, and the version of MySQL is 4.0.18. I manage it using phpMyAdmin. In addition, I also installed MySQL 4.1.10 on my local machine. Today, I backuped the remote database to backup.sql using phpMyAdmin. When I execute the sql script on the command-line using MySQL command on my local machine like this(MySQL 4.1.10):

The text data which contains Chinese characters has been changed to unreadable code. However, When I copy the sql script from backup.sql and paste them into phpMyAdmin, it can execute correctly and no unreadable code. Finally, I downloaded the whole database to local disk and copied them to “data” directory of MySQL. There is no error when I browse the data using phpMyAdmin. Why? I’m confused for a long time.

I thought that it must be some error of client-side character set when connect to MySQL server. So, I searched the web for the solution to set client-side character set and I found the following document.

 Connection Character Sets and Collations

Several character set and collation system variables relate to a client’s interaction with the server. Some of these have been mentioned in earlier sections:

Additional character set and collation variables are involved in handling traffic for the connection between a client and the server. Every client has connection-related character set and collation variables.

Consider what a “connection'’ is: It’s what you make when you connect to the server. The client sends SQL statements, such as queries, over the connection to the server. The server sends responses, such as result sets, over the connection back to the client. This leads to several questions about character set and collation handling for client connections, each of which can be answered in terms of system variables:

You can fine-tune the settings for these variables, or you can depend on the defaults (in which case, you can skip this section).

There are two statements that affect the connection character sets:

SET NAMES 'charset_name‘
SET CHARACTER SET charset_name

SET NAMES indicates what is in the SQL statements that the client sends. Thus, SET NAMES 'cp1251' tells the server “future incoming messages from this client are in character set cp1251.'’ It also specifies the character set for results that the server sends back to the client. (For example, it indicates what character set column values are if you use a SELECT statement.)

A SET NAMES 'x‘ statement is equivalent to these three statements:

mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x;

Setting character_set_connection to x also sets collation_connection to the default collation for x.

SET CHARACTER SET is similar but sets the connection character set and collation to be those of the default database. A SET CHARACTER SET x statement is equivalent to these three statements:

mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;

When a client connects, it sends to the server the name of the character set that it wants to use. The server sets the character_set_client, character_set_results, and character_set_connection variables to that character set. (In effect, the server performs a SET NAMES operation using the character set.)

With the mysql client, it is not necessary to execute SET NAMES every time you start up if you want to use a character set different from the default. You can add the --default-character-set option setting to your mysql statement line, or in your option file. For example, the following option file setting changes the three character set variables set to koi8r each time you run mysql:

[mysql]
default-character-set=koi8r

Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET latin1, then just before sending results back, the server converts the latin2 values to latin1. Conversion may be lossy if there are characters that are not in both character sets.

If you do not want the server to perform any conversion, set character_set_results to NULL:

mysql> SET character_set_results = NULL;

When I followed the method accroding to this document. It works fine, there is no unreadable code again. Ok, there are two solutions to achieve this:

The first solution is like this:

The shortcoming of this solution is that you must execute SET NAMES every time you start up. So, the second solution is add the –default-character-set option setting in the [client] section of my.ini file. Like this:

After setting this, restart the MySQL service. Now, you can execute an sql script file on the command-line directly without executing “SET NAMES” every time.