Linux Mysql5.6.42修改字符集

1. 进入/etc目录下

cd /etc

2. 打开my.cnf文件

vim my.cnf

3. 修改以下部分(Mysql5.6.42默认的配置文件中可能没有[client和[mysql]，直接添加即可)

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8

[mysqld]
character_set_server = utf8
character_set_client = utf8
collation-server = utf8_general_ci

[mysql]
no-auto-rehash
default-character-set=utf8

# 将这段话注释掉，否则将会报错
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

4. 重启数据库

service mysql restart

5. 查看字符集

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)

修改成功

6. 查数据库中的数据，看是否能显示

mysql> use db01;

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | z1   |
|  2 | ??   |
+----+------+
2 rows in set (0.00 sec)

发现中文字符还是显示乱码，原因是因为这个库是我们在修改字符集之前创建的，其编码方式仍不失utf8，重新建库进行测试。

mysql> create database db02;
Query OK, 1 row affected (0.00 sec)

mysql> use db02;
Database changed
mysql> create table user(id int not null, name varchar(20));
Query OK, 0 rows affected (0.09 sec)

mysql> select * from user;
Empty set (0.00 sec)

mysql> insert into user values(1,'张三');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

测试成功

7. 我们最好在安装好Mysql之后就立即对数据库的编码方式进行修改。因为在修改字符集之前创建的数据库中文显示仍将是乱码。