mysql亂碼問題

原文：http://www.jb51.net/article/31181.htm

我在navicat中的測試：

mysql> use code;
Database changed
mysql> set names `utf8`;
Query OK, 0 rows affected

mysql> select * from t2;
+--------------+
| test         |
+--------------+
| 測試utf8     |
| 阿薩斯       |
| 測試sdsdsgbk |
+--------------+
3 rows in set

mysql> insert into t2 values(`cat不不不utf8`);
Query OK, 1 row affected

mysql> select * from t2;
+---------------+
| test          |
+---------------+
| 測試utf8      |
| 阿薩斯        |
| 測試sdsdsgbk  |
| cat不不不utf8 |
+---------------+
4 rows in set

mysql> set names `gbk
`;
Query OK, 0 rows affected

mysql> insert into t2 values(`cat不不不gbk
`);
Query OK, 1 row affected

mysql> select * from t2;
+---------------+
| test          |
+---------------+
| ����utf8      |
| ����˹          |
| ����sdsdsgbk  |
| cat������utf8 |
| cat不不不gbk  |
+---------------+
5 rows in set

mysql> set names `utf8`;
Query OK, 0 rows affected

mysql> select * from t2;
+-----------------+
| test            |
+-----------------+
| 測試utf8        |
| 阿薩斯          |
| 測試sdsdsgbk    |
| cat不不不utf8   |
| cat涓嶄笉涓峠bk |
+-----------------+
5 rows in set

mysql> select * from t2;
+-----------------+
| test            |
+-----------------+
| 測試utf8        |
| 阿薩斯          |
| 測試sdsdsgbk    |
| cat不不不utf8   |
| cat涓嶄笉涓峠bk |
| cmd積極gbk      |
+-----------------+
6 rows in set

mysql> show variables like `character%`;
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | utf8                                                          |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | utf8                                                          |
| character_set_system     | utf8                                                          |
| character_sets_dir       | D:Program Files (x86)MySQLMySQL Server 5.0sharecharsets |
+--------------------------+---------------------------------------------------------------+
8 rows in set

mysql> 

在cmd中的測試：

mysql> set names `utf8`;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(`測試sdsdsutf8`);
ERROR 1406 (22001): Data too long for column `test` at row 1
mysql> set names `gbk`;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(`測試sdsdsgbk`);
Query OK, 1 row affected (0.32 sec)

mysql> select * from t2;
+--------------+
| test         |
+--------------+
| 測試utf8     |
| 阿薩斯       |
| 測試sdsdsgbk |
+--------------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+-----------------+
| test            |
+-----------------+
| 測試utf8        |
| 阿薩斯          |
| 測試sdsdsgbk    |
| cat不不不utf8   |
| cat涓嶄笉涓峠bk |
+-----------------+
5 rows in set (0.00 sec)

mysql> set names `gbk`;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+-----------------+
| test            |
+-----------------+
| 測試utf8        |
| 阿薩斯          |
| 測試sdsdsgbk    |
| cat不不不utf8   |
| cat涓嶄笉涓峠bk |
+-----------------+
5 rows in set (0.00 sec)

mysql> set names `utf8`;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+----------------------+
| test                 |
+----------------------+
| 嫻嬭瘯utf8           |
| 闃胯惃鏂?           |
| 嫻嬭瘯sdsdsgbk       |
| cat涓嶄笉涓島tf8     |
| cat娑撳秳絎夋稉宄燽k |
+----------------------+
5 rows in set (0.00 sec)





mysql> set names `utf8`;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+----------------------+
| test                 |
+----------------------+
| 嫻嬭瘯utf8           |
| 闃胯惃鏂?           |
| 嫻嬭瘯sdsdsgbk       |
| cat涓嶄笉涓島tf8     |
| cat娑撳秳絎夋稉宄燽k |
+----------------------+
5 rows in set (0.00 sec)

mysql> insert into t2 values(`cmd積極utf8`);
ERROR 1406 (22001): Data too long for column `test` at row 1
mysql> insert into t2 values(`cmd積極utf8`);
ERROR 1406 (22001): Data too long for column `test` at row 1
mysql> set names `gbk`;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(`cmd積極gbk`);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;
+-----------------+
| test            |
+-----------------+
| 測試utf8        |
| 阿薩斯          |
| 測試sdsdsgbk    |
| cat不不不utf8   |
| cat涓嶄笉涓峠bk |
| cmd積極gbk      |
+-----------------+
6 rows in set (0.00 sec)

mysql> show variables like `charcate%`;
Empty set (0.00 sec)

mysql> show variables like `character%`;
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | gbk                                                           |
| character_set_connection | gbk                                                           |
| character_set_database   | utf8                                                          |
| character_set_filesystem | binary                                                        |
| character_set_results    | gbk                                                           |
| character_set_server     | utf8                                                          |
| character_set_system     | utf8                                                          |
| character_sets_dir       | D:Program Files (x86)MySQLMySQL Server 5.0sharecharsets |
+--------------------------+---------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql>