数椐库乱码问题

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