关于mysql在linux下面字符修改的问题

You inserted CJK characters with INSERT, but when you do a SELECT, they all look like “?”. It usually is a setting in MySQL that doesn't match the settings for the application program or the operating system. These are common troubleshooting steps:

• Find out: what version do you have? The statement SELECT VERSION(); will tell you. This FAQ is for MySQL version 5, so some of the answers here will not apply to you if you have version 4.0 or 4.1.

• Find out: what character set is the database column really in? Too frequently, people think that the character set will be the same as the server's set (false), or the set used for display purposes (false). Make sure, by saying SHOW CREATE TABLE tablename, or better yet by saying this:

  SELECT character_set_name, collation_nameFROM   information_schema.columns WHERE  table_schema = your_database_nameAND    table_name = your_table_nameAND    column_name = your_column_name;

   

• Find out: what is the hexadecimal value?

  SELECT HEX(your_column_name)FROM your_table_name;

  If you see 3F, then that really is the encoding for ?, so no wonder you see “?”. Probably this happened because of a problem converting a particular character from your client character set to the target character set.

• Find out: is a literal round trip possible, that is, if you select “literal” (or “_introducer hexadecimal-value”) do you get “literal” as a result? For example, with the Japanese Katakana Letter Pe, which looks like ペ', and which exists in all CJK character sets, and which has the code point value (hexadecimal coding) 0x30da, enter:

  SELECT 'ペ' AS `ペ`;         /* or SELECT _ucs2 0x30da; */

  If the result doesn't look like ペ, a round trip failed. For bug reports, we might ask people to follow up with SELECT hex('ペ');. Then we can see whether the client encoding is right.

• Find out: is it the browser or application? Just use mysql (the MySQL client program, which on Windows will be mysql.exe). If mysql displays correctly but your application doesn't, then your problem is probably “Settings”, but consult also the question about “Troubles with Access (or Perl) (or PHP) (etc.)” much later in this FAQ.

  To find your settings, the statement you need here is SHOW VARIABLES. For example:

  mysql> SHOW VARIABLES LIKE 'char%';+--------------------------+----------------------------------------+| Variable_name            | Value                                  |+--------------------------+----------------------------------------+| character_set_client     | utf8                                   || character_set_connection | utf8                                   || character_set_database   | latin1                                 || character_set_filesystem | binary                                 || character_set_results    | utf8                                   || character_set_server     | latin1                                 || character_set_system     | utf8                                   || character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |+--------------------------+----------------------------------------+8 rows in set (0.03 sec)

  The above are typical character-set settings for an international-oriented client (notice the use of utf8 Unicode) connected to a server in the West (latin1 is a West Europe character set and a default for MySQL).

  Although Unicode (usually the utf8 variant on Unix, usually the ucs2 variant on Windows) is better than “latin”, it's often not what your operating system utilities support best. Many Windows users find that a Microsoft character set, such as cp932 for Japanese Windows, is what's suitable.

  If you can't control the server settings, and you have no idea what your underlying computer is about, then try changing to a common character set for the country that you're in (euckr = Korea, gb2312 or gbk = People's Republic of China, big5 = other China, sjis or ujis or cp932 or eucjpms = Japan, ucs2 or utf8 = anywhere). Usually it is only necessary to change the client and connection and results settings, and there is a simple statement which changes all three at once, namely SET NAMES. For example:

  SET NAMES 'big5';

  Once you get the correct setting, you can make it permanent by editing my.cnf or my.ini. For example you might add lines looking like this:

  [mysqld]character-set-server=big5[client]default-character-set=big5