目录
1.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
2.修改字符集
临时修改
-- 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
3.MySQL数据库中字符集转换流程
- MySQL收到请求时将请求数据从
character_set_client
转换为character_set_connection
- 进行内部操作前将请求数据从
character_set_connection
转换为内部操作字符集,其确定方法如下- 使用每个
数据字段的CHARACTER SET
设定值 - 若上述值不存在,则使用对应
数据表的DEFAULT CHARACTER SET
设定值(MySQL扩展,非SQL标准) - 若上述值不存在,则使用对应
数据库的DEFAULT CHARACTER SET
设定值 - 若上述值不存在,则使用
character_set_server
设定值
- 使用每个
- 将操作结果从内部操作字符集转换为
character_set_connection
- 将响应数据从
character_set_connection
转为character_set_client
执行SQL语句时信息的路径是这样的
信息输入路径:client → connection → server;
信息输出路径:server → connection → results.
4.修改现有字符集
修改数据库的字符集
-- 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;