centos6.3系统mysql数据库字符集设置,彻底解决乱码情况

时间:2022-09-22 22:29:11

转载自:http://www.tuicool.com/articles/6b2Qvm

今天在部署一个系统时,网页出现了乱码。于是各种百度(之前一直用同一种方式,但是没影响使用),

下面做个试验。

本服务器系统是centos6.3,lamp环境全部用yum安装。没有优化过任任何配置

下面看mysql默认字符集配置

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> show variables like 'collation_%';

+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql > show variables like "%char%" ;

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

| Variable_name              | Value                        |

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

| character_set_client      | latin1                      |

| character_set_connection | latin1                      |

| character_set_database    | latin1                      |

| character_set_filesystem | binary                      |

| character_set_results      | latin1                      |

| character_set_server      | latin1                      |

| character_set_system      | utf8                        |

| character_sets_dir        | /usr / share / mysql / charsets/ |

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

8 rows in set (0.00 sec)

mysql > show variables like 'collation_%' ;

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

| Variable_name          | Value              |

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

| collation_connection | latin1_swedish_ci |

| collation_database    | latin1_swedish_ci |

| collation_server      | latin1_swedish_ci |

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

3 rows in set (0.00 sec)

接下来我们建一个数据库

mysql> use test;
Database changed
mysql> CREATE TABLE `test_user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'email',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='测试数据库字符集';


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_user |
+----------------+
1 rows in set (0.00 sec)

mysql > use test;

Database changed

mysql > CREATE TABLE `test_user` (

   `id` int (10) unsigned NOT NULL AUTO_INCREMENT ,

   `username` varchar (255) CHARACTER SET utf8 DEFAULT NULL ,

   `email` varchar (255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'email' ,

   PRIMARY KEY (`id`)

) ENGINE = MyISAM DEFAULT CHARSET = utf8 COMMENT = '测试数据库字符集' ;

mysql > show tables ;

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

| Tables_in_test |

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

| test_user        |

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

1 rows in set (0.00 sec)

插入两条测试数据

INSERT INTO `test`.`test_user` (`id`, `username`, `email`) VALUES ('1', '乔布斯', 'jobs@apple.com');
INSERT INTO `test`.`test_user` (`id`, `username`, `email`) VALUES ('2', '苹果', 'apple@apple.com');

INSERT INTO `test`.`test_user` (`id`, `username`, `email`) VALUES ( '1' , '乔布斯' ,'jobs@apple.com' );

INSERT INTO `test`.`test_user` (`id`, `username`, `email`) VALUES ( '2' , '苹果' ,'apple@apple.com' );

查询显示乱码,但在我们的网页中不影响使用(因为此表中的数据字符集为utf8,)

mysql> select * from test_user;
+----+----------+-----------------+
| id | username | email |
+----+----------+-----------------+
| 1 | ??? | jobs@apple.com |
| 2 | ?? | apple@apple.com |
+----+----------+-----------------+
2 rows in set (0.00 sec)

mysql > select * from test_user;

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

| id | username | email            |

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

|    1 | ???        | jobs@apple.com    |

|    2 | ??        | apple@apple.com |

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

2 rows in set (0.00 sec)

我们做查询时用set names utf8命令

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql > set names utf8;

Query OK, 0 rows affected (0.00 sec)

结果显示正常

mysql> select * from test_user;
+----+-----------+-----------------+
| id | username | email |
+----+-----------+-----------------+
| 1 | 乔布斯 | jobs@apple.com |
| 2 | 苹果 | apple@apple.com |
+----+-----------+-----------------+
2 rows in set (0.00 sec)

mysql > select * from test_user;

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

| id | username    | email            |

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

|    1 | 乔布斯 | jobs@apple.com    |

|    2 | 苹果      | apple@apple.com |

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

2 rows in set (0.00 sec)

查看此时字符集情况

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| 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)

mysql> show variables like 'collation_%';

+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql > show variables like "%char%" ;

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

| Variable_name              | Value                        |

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

| character_set_client      | utf8                        |

| character_set_connection | utf8                        |

| character_set_database    | latin1                      |

| 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)

mysql > show variables like 'collation_%' ;

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

| Variable_name          | Value              |

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

| collation_connection | utf8_general_ci    |

| collation_database    | latin1_swedish_ci |

| collation_server      | latin1_swedish_ci |

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

3 rows in set (0.00 sec)

现在可以看到client,connection,rsults这三个字符集都已经是utf8(此时我们已经可以正常使用了),但是为了预防一些不正常的插入数据,建议大家把数据库全部设置成utf8,免去一些排查错的麻烦。

[root@test ~]# vim /etc/my.cnf 改成如下

[client]
default-character-set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default_character_set=utf8
[mysql]
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]

default - character - set = utf8

[mysqld]

datadir=/var / lib / mysql

socket =/var / lib / mysql / mysql.sock                                                                                                                                                                                                            

user = mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic - links = 0

default_character_set = utf8

[mysql]

default - character - set = utf8

[mysqld_safe]

log - error=/var / log / mysqld. log

pid - file =/var / run / mysqld / mysqld.pid

主要加了以下数据

[client]
default_character_set=utf8
[mysql]
default_character_set=utf8
[mysqld]
default_character_set=utf8

[client]

default_character_set = utf8

[mysql]

default_character_set = utf8

[mysqld]

default_character_set = utf8

重启mysql服务

[root@test ~]# /etc/init.d/mysqld restart

停止 mysqld: [确定]

正在启动 mysqld: [确定]

此时查看字符集的情况

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 | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

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      | utf8                        |

| character_set_system      | utf8                        |

| character_sets_dir        | /usr / share / mysql / charsets/ |

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

8 rows in set (0.00 sec)

查看校对字符集

mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql > show variables like 'collation_%' ;

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

| Variable_name          | Value            |

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

| collation_connection | utf8_general_ci |

| collation_database    | utf8_general_ci |

| collation_server      | utf8_general_ci |

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

3 rows in set (0.00 sec)

此时数据库字符集已经全部统一成utf8

MySQL的字符集支持(Character Set Support)有两个方面:

字符集(Character set)和排序方式(Collation)。

对于字符集的支持细化到四个层次:

服务器(server),数据库(database),数据表(table)和连接(connection)。

1.MySQL默认字符集

MySQL对于字符集的指定可以细化到一个数据库,一张表,一列,应该用什么字符集。

但是,传统的程序在创建数据库和数据表时并没有使用那么复杂的配置,它们用的是默认的配置,那么,默认的配置从何而来呢?

(1)编译MySQL 时,指定了一个默认的字符集,这个字符集是 latin1;

(2)安装MySQL 时,可以在配置文件 (my.ini) 中指定一个默认的的字符集,如果没指定,这个值继承自编译时指定的;

(3)启动mysqld 时,可以在命令行参数中指定一个默认的的字符集,如果没指定,这个值继承自配置文件中的配置,此时 character_set_server 被设定为这个默认的字符集;

(4)当创建一个新的数据库时,除非明确指定,这个数据库的字符集被缺省设定为character_set_server;

(5)当选定了一个数据库时,character_set_database 被设定为这个数据库默认的字符集;

(6)在这个数据库里创建一张表时,表默认的字符集被设定为 character_set_database,也就是这个数据库默认的字符集;

(7)当在表内设置一栏时,除非明确指定,否则此栏缺省的字符集就是表默认的字符集;

简单的总结一下,如果什么地方都不修改,那么所有的数据库的所有表的所有栏位的都用

latin1 存储,不过我们如果安装 MySQL,一般都会选择多语言支持,也就是说,安装程序会自动在配置文件中把

default_character_set 设置为 UTF-8,这保证了缺省情况下,所有的数据库的所有表的所有栏位的都用 UTF-8 存储。