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)\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------------+
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)\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql>