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)
问题解决!