centos mysql 字符集设置_CentOS7修改mysql5.6字符集

解决办法：CentOS7下修改MySQL数据库字符编码为UTF-8，UTF-8包含全世界所有国家所需要的字符集，是国际编码。

具体操作如下：

1.进入MySQL

[root@tianqi-01 ~]# mysql -uroot -p

Enter password:             #输入密码

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

2.查看当前MySQL运行状态

mysql> status;

--------------

mysql  Ver 14.14 Distrib 5.6.35, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:        4

Current database:

Current user:        root@localhost

SSL:            Not in use

Current pager:        stdout

Using outfile:        ''

Using delimiter:    ;

Server version:        5.6.35-log MySQL Community Server (GPL)

Protocol version:    10

Connection:        Localhost via UNIX socket

Server characterset:    latin1

Db     characterset:    latin1

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:        /tmp/mysql.sock

Uptime:            16 min 19 sec

Threads: 3  Questions: 26  Slow queries: 0  Opens: 82  Flush tables: 1  Open tables: 75  Queries per second avg: 0.026

--------------

mysql>

3.修改MySQL配置文件

[root@tianqi-01 ~]# vim /etc/my.cnf

[client]

default-character-set=utf8

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

datadir=/data/mysql

socket=/tmp/mysql.sock

server-id=130

log_bin=aminglinux1

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

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

#log-error=/var/log/mariadb/mariadb.log

#pid-file=/var/run/mariadb/mariadb.pid

#

# include all files from the config directory

#

#!includedir /etc/my.cnf.d

保存退出

*我的MySQL里面没有[client]字段，自己添加上，在[client]字段下面添加default-character-set=utf8

*在[mysqld]字段下面添加

character-set-server=utf8

collation-server=utf8_general_ci

*保存退出

4.重启MySQL服务

[root@tianqi-01 ~]# /etc/init.d/mysqld restart

Shutting down MySQL..                                      [  OK  ]

Starting MySQL..                                           [  OK  ]

[root@tianqi-01 ~]#

5.查看当前MySQL运行状态

mysql> status;

--------------

mysql  Ver 14.14 Distrib 5.6.35, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:        3

Current database:

Current user:        root@localhost

SSL:            Not in use

Current pager:        stdout

Using outfile:        ''

Using delimiter:    ;

Server version:        5.6.35-log MySQL Community Server (GPL)

Protocol version:    10

Connection:        Localhost via UNIX socket

Server characterset:    utf8

Db     characterset:    utf8

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:        /tmp/mysql.sock

Uptime:            49 sec

Threads: 3  Questions: 5  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.102

--------------

mysql>

此时所有编码应该都是UTF-8

参数说明：

haracter_set_client：客户端请求数据的字符集。

character_set_connection：从客户端接收到数据，然后传输的字符集。

character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，使character_set_server指定的字符集，此参数无需设置。

character_set_filesystem：把操作系统上文件名转化成此字符集，即把character_set_client转换character_set_filesystem，默认binary即可。

character_set_results：结果集的字符集。

character_set_server：数据库服务器的默认字符集。

character_set_system：这个值总是utf8，不需要设置，存储系统元数据的字符集。