centos 7.2中xampp mysql 数据字符集设置解决中文乱码

centos 7.2中xampp mysql 数据字符集设置解决中文乱码

从终端进入mysql

[root@VM_172_94_centos etc]# /opt/lampp/bin/mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.21-MariaDB Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'char_%';

centos系统中mysql默认字符集配置

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /opt/lampp/share/charsets/ |
+--------------------------+----------------------------+

**使用xampp集成环境搭建lamp，MySQL中的my.cnf目录为 **

/opt/lampp/etc

我的数据库为MariaDB 10.1.21

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.21-MariaDB Source distribution

使用vim修改配置

[root@VM_172_94_centos etc]# vim /opt/lampp/etc/my.cnf

网上的修改方法，适用于MySQL5.7之前，修改之后会导致MySQL 无法启动

我的修改方法为

[client]
default-character-set=utf8

[mysqld]
collation-server=utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server=utf8

[mysql]
default-character-set=utf8

重新启动MySQL

[root@VM_172_94_centos etc]# /opt/lampp/lampp restart
Restarting XAMPP for Linux 5.6.30-1...
XAMPP: Stopping Apache...ok.
XAMPP: Stopping MySQL...ok.
XAMPP: Stopping ProFTPD...ok.
XAMPP: Starting Apache...ok.
XAMPP: Starting MySQL...ok.
XAMPP: Starting ProFTPD...ok.

登陆MySQL并查看字符集

[root@VM_172_94_centos etc]# /opt/lampp/bin/mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.21-MariaDB Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 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 | /opt/lampp/share/charsets/ |
+--------------------------+----------------------------+

成功 latinl 改为了 UTF-8