mysql中文乱码问题

1、在创建数据库后，就是使用数据库，首先插入一条记录，然后查看：

mysql> insert into CmsUser(username,password,area) value ("admin2","123456","北 京");
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from CmsUser;
+--------+----------+----------+------+----------+
| userid | username | password | area | deptrole |
+--------+----------+----------+------+----------+
|      1 | mzuser   | NULL     | NULL | NULL     |
|      2 | mzuser   | 123456   | ??   | ??       |
|      3 | admin    | 123456   | ??   | NULL     |
|      4 | admin1   | 123456   | ??   | NULL     |
|      5 | admin2   | 123456   | ??   | NULL     |
+--------+----------+----------+------+----------+
5 rows in set (0.00 sec)

 发现中文插入后不显示

2、查看数据库的编码方式，为latin1

mysql> show create database example;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| example  | CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

 查看表的编码方式，发现为latin1

mysql> show create table CmsUser;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                             |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CmsUser | CREATE TABLE `CmsUser` (
  `userid` int(8) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) DEFAULT NULL,
  `area` varchar(10) DEFAULT NULL,
  `deptrole` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 3、修改数据库和表的编码方式为utf8

mysql> alter database example character set utf8;
Query OK, 5 rows affected (0.17 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> alter table CmsUser character set utf8;
Query OK, 5 rows affected (0.17 sec)
Records: 5  Duplicates: 0  Warnings: 0

 

alter table CmsUser modify column area varchar(10) character set utf8; 

 

4、为了能够一次修改后以后都使用中文，也可以直接修改配置文件/etc/mysql/my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set=utf8                         //添加内容

[mysqld]
#default-character-set=utf8                        //添加内容，但启动失败
character-set-server=utf8                          //添加内容

    然后重启数据库

      /etc/init.d/mysql restart

   查看数据库字符集，其中client和database都成了utf8

mysql> show variables like "%char%"
    -> ;
+--------------------------+----------------------------+
| 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     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

 

5、 删除所有数据后重新插入

mysql> insert into CmsUser(username,password,area) value ("admin","123456","北京");
Query OK, 1 row affected (0.03 sec)

mysql> select * from CmsUser;
+--------+----------+----------+--------+----------+
| userid | username | password | area   | deptrole |
+--------+----------+----------+--------+----------+
|      1 | admin    | 123456   | 北京   | NULL     |
+--------+----------+----------+--------+----------+
1 row in set (0.00 sec)

    问题解决！