mysql中文字元的問題全面處理

mysql中文字元的問題
mysql5.1上成功更改的例子：      
      1.在my.cnf中clent和mysqld加入default-character-set=utf8

      2.  更改資料庫字符集

      3.建立資料庫表字符集

      4.更改相容中文的gbk

以下是詳細過程

 

mysql> use mysql

Database changed

mysql> CREATE TABLE pingfen

    ->    (CODE VARCHAR(20),

    -> ZONGHEPINGFEN Decimal(12,3),

    -> DATE_INSERT DATEtime,

    -> NEIRONG LONGTEXT,

    -> JISHU Decimal(12,3),

    -> ZIJIN Decimal(12,3),

    -> XIAOXI Decimal(12,3),

    -> HANGYE Decimal(12,3),

    -> JIBEN Decimal(12,3),

    -> PAIXU_TIPS Decimal(12,3),

    -> DUANQI VARCHAR(100),

    -> ZHONGQI VARCHAR(100),

    -> CHANGQI VARCHAR(100),

    -> CHENGBEN Decimal(12,3),

    -> CHENGBENMIAOSHU VARCHAR(500),

    -> WURIZIJIN VARCHAR(100)

    ->    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into table pingfen(zonghepingfen,duanqi) values(6,'你好');

    '> ;

    '> ;

    '> /

    '> ;

    '> exit

    '> exit;

    '> by;

    '> Ctrl-C -- exit!

Aborted

[root@master yum.repos.d]# mysql -uroot

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.1.71 Source distribution

 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> use mysql mysql

Database changed

mysql> insert into  pingfen(zonghepingfen,duanqi) values(6,'你好');

Query OK, 1 row affected, 1 warning (0.01 sec)

 

mysql> select * from pingfen;

+------+---------------+-------------+---------+-------+-------+--------+--------+-------+------------+--------+---------+---------+----------+-----------------+-----------+

| CODE | ZONGHEPINGFEN | DATE_INSERT | NEIRONG | JISHU | ZIJIN | XIAOXI | HANGYE | JIBEN | PAIXU_TIPS | DUANQI | ZHONGQI | CHANGQI | CHENGBEN | CHENGBENMIAOSHU | WURIZIJIN |

+------+---------------+-------------+---------+-------+-------+--------+--------+-------+------------+--------+---------+---------+----------+-----------------+-----------+

| NULL |         6.000 | NULL        | NULL    |  NULL |  NULL |   NULL |   NULL |  NULL |       NULL |        | NULL    | NULL    |     NULL | NULL            | NULL      |

+------+---------------+-------------+---------+-------+-------+--------+--------+-------+------------+--------+---------+---------+----------+-----------------+-----------+

1 row in set (0.00 sec)

 

mysql> set names utf8;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from pingfen;

+------+---------------+-------------+---------+-------+-------+--------+--------+-------+------------+--------+---------+---------+----------+-----------------+-----------+

| CODE | ZONGHEPINGFEN | DATE_INSERT | NEIRONG | JISHU | ZIJIN | XIAOXI | HANGYE | JIBEN | PAIXU_TIPS | DUANQI | ZHONGQI | CHANGQI | CHENGBEN | CHENGBENMIAOSHU | WURIZIJIN |

+------+---------------+-------------+---------+-------+-------+--------+--------+-------+------------+--------+---------+---------+----------+-----------------+-----------+

| NULL |         6.000 | NULL        | NULL    |  NULL |  NULL |   NULL |   NULL |  NULL |       NULL |        | NULL    | NULL    |     NULL | NULL            | NULL      |

+------+---------------+-------------+---------+-------+-------+--------+--------+-------+------------+--------+---------+---------+----------+-----------------+-----------+

1 row in set (0.00 sec)

 

mysql> select duanqi from pingfen;

+--------+

| duanqi |

+--------+

|        |

+--------+

1 row in set (0.00 sec)

 

mysql> insert into  pingfen(zonghepingfen,duanqi) values(6,'你好111111111111111111');

Query OK, 1 row affected, 1 warning (0.01 sec)

 

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select duanqi from pingfen;

+--------+

| duanqi |

+--------+

|        |

|        |

+--------+

2 rows in set (0.00 sec)

 

mysql> set names gbk;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select duanqi from pingfen;

+--------+

| duanqi |

+--------+

|        |

|        |

+--------+

2 rows in set (0.00 sec)

 

mysql> alter table pingfen character set gbk;    更改相容中文的gbk

Query OK, 2 rows affected (0.07 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

mysql> select duanqi from pingfen;

+--------+

| duanqi |

+--------+

|        |

|        |

+--------+

2 rows in set (0.00 sec)

 

mysql> insert into  pingfen(zonghepingfen,duanqi) values(6,'你好111111111111111111');

Query OK, 1 row affected (0.01 sec)

 

mysql> select duanqi from pingfen;

+------------------------+

| duanqi                 |

+------------------------+

|                        |

|                        |

| 你好111111111111111111     |

+------------------------+

3 rows in set (0.00 sec)

 

mysql>