character_set_database和character_set_server的默认字符集是latin1
最简单的完美修改方法，修改mysql的my.cnf：
在[client]字段里加入default-character-set = utf8
在[mysqld]字段里加入character-set-server=utf8
vi /etc/my.cnf [client] #password = your_password default-character-set = utf8 [mysqld] #skip-grant-tables character-set-server = utf8
注意需要重启数据库：
mysql> show variables like "character_set_%"; +--------------------------+----------------------------------------------+ | 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 | /alidata/server/mysql-5.5.40/share/charsets/ | +--------------------------+----------------------------------------------+ 8 rows in set (0.00 sec)
但是还是不能显示中:
mysql> insert into articles (id,content) values (15,'全备'); Query OK, 1 row affected, 1 warning (0.22 sec) mysql> select * from articles; +----+---------+ | id | content | +----+---------+ | 11 | ha | | 12 | xi | | 13 | ai | | 14 | ho | | 15 | ?? | +----+---------+ 5 rows in set (0.00 sec)
mysql> show create table articles; +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | articles | CREATE TABLE `articles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` longtext NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 | +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
因为表的本身的字符集就是latin1
还有个问题 就是在mysql>的壮态下输入中文的时候会出现乱码 如：
经过测试，应变是系统的问题，关闭所有的shell，再重新开启问题解决！
=============================================================
之前做过的实验： [root@12k ~]# cat /etc/sysconfig/i18n LANG="zh_CN.GB2312" #LANG="en_US.UTF-8" SYSFONT="latarcyrheb-sun16" [root@12k ~]# mysql -uroot -p123 -e "show variables like 'character_set_%';" +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | gb2312 | | character_set_connection | gb2312 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | gb2312 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ [root@12k ~]# mysql -uroot -p123 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 31 Server version: 5.5.20-log Source distribution Copyright (c) 2000, 2011, 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> show variables like 'character_set_%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | gb2312 | | character_set_connection | gb2312 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | gb2312 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'character_set_%'; +--------------------------+----------------------------------+ | 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 | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) mysql> mysql> exit #退出再登陆 发现又恢复原来的字符集所以，说set names ***是临时的 Bye [root@12k ~]# mysql -uroot -p123 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 32 Server version: 5.5.20-log Source distribution Copyright (c) 2000, 2011, 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> show variables like 'character_set_%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | gb2312 | | character_set_connection | gb2312 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | gb2312 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) mysql> 下面如果代上--default-character-set=utf8 [root@12k ~]# mysql -uroot -p123 --default-character-set=utf8 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 33 Server version: 5.5.20-log Source distribution Copyright (c) 2000, 2011, 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> show variables like 'character_set_%'; +--------------------------+----------------------------------+ | 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 | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) mysql> 其实它跟set names *** 的性质是一样的 只是在登录的时候就设置了（临时） 从则面也说明linux的字符集不是关键，建库和建表的字符集才是关键。 永久设置 [root@12k ~]# sed -En '/client|default-characte/p' /etc/my.cnf # The following options will be passed to all MySQL clients [client] default-character-set = utf8 #在client下增加 [root@12k ~]# 看到linux系统的字符集如下 [root@12k ~]# cat /etc/sysconfig/i18n LANG="zh_CN.GB2312" #LANG="en_US.UTF-8" SYSFONT="latarcyrheb-sun16" [root@12k ~]# mysql -uroot -p123 -e "show variables like 'character_set_%';" +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8 [clinet] | | character_set_connection | utf8 [clinet] | | character_set_database | utf8 [mysqld] | | character_set_filesystem | binary | | character_set_results | utf8 [client] | | character_set_server | utf8 [mysqld] | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 可以看到字符集已经统一 最后是mysql的服务端：但是需要重启 [root@12k ~]# sed -nE '/mysqld|^character-set/p' /etc/my.cnf [mysqld] character-set-server = utf8 #在/etc/my.cnf 的[mysqd]下增加 # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # (via the "enable-named-pipe" option) will render mysqld useless! [mysqldump] 只有重启才能把配置文件 加载到内存中 其中服务端是改的 character_set_database 和 character_set_server这两个。 也把linux系统的字符集也改过来： [root@12k ~]# cat /etc/sysconfig/i18n #LANG="zh_CN.GB2312" LANG="en_US.UTF-8" SYSFONT="latarcyrheb-sun16" [root@12k ~]# source /etc/sysconfig/i18n