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

最新推荐文章于 2022-01-13 17:20:10 发布

一枪尽骚丶魂

最新推荐文章于 2022-01-13 17:20:10 发布

阅读量767

点赞数

分类专栏： Linux mysql

Linux 同时被 2 个专栏收录

192 篇文章 3 订阅

订阅专栏

mysql

81 篇文章 0 订阅

订阅专栏

转载自：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 存储。

一枪尽骚丶魂

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
centos6.3系统mysql数据库字符集设置,彻底解决乱码情况

转载自：http://www.tuicool.com/articles/6b2Qvm今天在部署一个系统时，网页出现了乱码。于是各种百度（之前一直用同一种方式，但是没影响使用），下面做个试验。本服务器系统是centos6.3，lamp环境全部用yum安装。没有优化过任任何配置下面看mysql默认字符集配置mysql> show variables li
复制链接

扫一扫