可以通过在数据库修改指定数据库编码格式或者修改配置文件my.cnf来修改编码。
查看数据库当前的编码格式:
登录数据库
执行sql
status;
或者
show variables like 'character%';
以后者为例,显示结果如下:
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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)
修改编码格式:
找到数据库的配置文件my.cnf,修改
重启数据库
查找mysql的配置文件地址:
mysql --verbose --help | grep -A 1 'Default options'
会给出配置读取的位置,按顺序,若前者不存在则往后查找
以rpm安装的mysql为例,配置文件一般在/etc/my.cnf
默认编码为latin1,应修改为utf-8。在文件中修改或增加如下配置:
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
collation-server=utf8_general_ci[client]
default-character-set=utf8
保存配置文件后,重启数据库:
systemctl restart mysqld
systemctl status mysqld.service //查看运行状态
此时再进入数据库查询当前编码:
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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)
查看指定数据库的编码格式:
show create database [数据库名称];
修改指定数据库的编码格式为utf-8:
alter database [数据库名称] default character set utf8 collate utf8_general_ci;