mysql编码格式查询和修改
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| 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 | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
参数说明:
character_set_client为客户端编码方式;
character_set_connection为建立连接使用的编码;
character_set_database数据库的编码;
character_set_results结果集的编码;
character_set_server数据库服务器的编码;
初始的mysql,会出现查询结果中文乱码,用中文作为参数查询不到数据的情况,只要保证以上四个采用的编码方式一样,就不会出现乱码问题,也可以用中文正常查询。
mysql> select * from SECURITY_ROLE limit 1;
+----------------------------------+---------------------+-------------+------------+-------+------------+
| ID | CREATE_TIME | DESCRIPTION | IDENTITY | NAME | SUPERVISOR |
+----------------------------------+---------------------+-------------+------------+-------+------------+
| ff8080815c7cf447015c7cf4824a0066 | 2017-06-06 18:30:17 | ????? | SUPERVISOR | ????? | |
+----------------------------------+---------------------+-------------+------------+-------+------------+
1 row in set (0.00 sec)
修改编码:
set names utf8;等同于:
set character_set_client=utf8;
set character_set_results=utf8;
set character_set_connection=utf8;
mysql> 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 | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
之后就可以正常查询了:
mysql> select * from SECURITY_ROLE limit 1;
+----------------------------------+---------------------+-----------------+------------+-----------------+------------+
| ID | CREATE_TIME | DESCRIPTION | IDENTITY | NAME | SUPERVISOR |
+----------------------------------+---------------------+-----------------+------------+-----------------+------------+
| ff8080815c7cf447015c7cf4824a0066 | 2017-06-06 18:30:17 | 超级管理员 | SUPERVISOR | 超级管理员 | |
+----------------------------------+---------------------+-----------------+------------+-----------------+------------+
1 row in set (0.00 sec)