MySQL字符集介绍及配置

MySQL编码集

查看MySQL支持的字符集

mysql> show character set;

查看MySQL当前的字符集

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)

或者使用status命令或者\s命令

上面的字符集是MySQL5.7.x安装好默认的字符集

命令的官网解释:

• character_set_client
• character_set_connection
• character_set_database
• character_set_filesystem
• character_set_results
• character_set_server
• character_set_system

修改字符集

临时修改

-- set [global] variable_name=charset;
mysql> set global character_set_server=utf8;

永久修改

在my.cnf文件中指定

[client]
default-character-set=utf8

影响参数:

• character_set_client
• character_set_connection
• character_set_results

[mysqld]
character-set-server=utf8

影响参数:

• character_set_database
• character_set_server

MySQL数据库中字符集转换流程

1. MySQL收到请求时将请求数据从character_set_client转换为character_set_connection
2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下
   1. 使用每个数据字段的CHARACTER SET设定值
   2. 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)
   3. 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值
   4. 若上述值不存在，则使用character_set_server设定值
3. 将操作结果从内部操作字符集转换为character_set_connection
4. 将响应数据从character_set_connection转为character_set_client

执行SQL语句时信息的路径是这样的

信息输入路径：client → connection → server;

信息输出路径：server → connection → results.

修改现有字符集

修改数据库的字符集

-- alter database db_name character set charset;
mysql> alter database snail character set utf8;

修改表的字符集

-- alter database table_name character set charset;
mysql> alter table people character set utf8;

修改列的字符集

-- alter table table_name change column_name column_name varchar(10) character set charset;
mysql> alter table people change name name varchar(10) character set utf8;