当前mysql实例的客户端和服务端的字符集
mysql> show global variables like "character_set_%";
+--------------------------+-----------------------------------------+ | 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 | /data/apps/mysql-5.7.22/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)
| character_set_client | latin1 | #这是mysql客户端(mysql)使用的字符集 | character_set_connection | latin1 | #这是mysql客户端(mysql)连接使用的字符集 | character_set_database | latin1 | #这是mysql数据库的字符集 | character_set_filesystem | binary | #这是文件系统的字符集(二进制) | character_set_results | latin1 | #这是mysql客户端最终使用的字符集 | character_set_server | latin1 | #这是mysql服务端使用的字符集 | character_set_system | utf8 | #这个是linux系统层面的字符集哈
修改mysql客户端和服务端的字符集
登录mysql用以下命令修改mysql服务端的字符集 【临时生效,只要一重启mysql服务就会失效】
mysql> set global character_set_database=utf8; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> set global character_set_server=utf8; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "character_set_%";
+--------------------------+-----------------------------------------+ | 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 | /data/apps/mysql-5.7.22/share/charsets/ | +--------------------------+-----------------------------------------+ 8 rows in set (0.00 sec)
登录mysql用以下命令修改mysql客户端的字符集 【临时生效,只要一重启mysql服务就会失效】
mysql> set global character_set_client=utf8; Query OK, 0 rows affected (0.00 sec) mysql> set global character_set_connection=utf8; Query OK, 0 rows affected (0.00 sec) mysql> set global character_set_results=utf8; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "character_set_%";
+--------------------------+-----------------------------------------+ | 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 | /data/apps/mysql-5.7.22/share/charsets/ | +--------------------------+-----------------------------------------+ 8 rows in set (0.00 sec)
修改my.cnf的配置文件
A:在my.cnf配置文件中的[mysqld]模块中添加character_set_server=utf8(这是设置mysql服务端的字符集为utf8字符集) B:在my.cnf配置文件中的[mysqld]模块中添加skip_character_set_client_handshake=1 #注意:这里是路过客户端的字符集,使用服务端的字符集 C:为mysql命令做别名:echo "alias mysql='mysql -A --default-character-set=utf8'" >>/etc/bashrc && source /etc/bashrc