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       | E:\MySQL\MySQL Server 5.6\share\charsets\ |
+--------------------------+-------------------------------------------+

查看当前支持的字符集:

show charset;

查看数据库/表创建时的sql语句:

show create database 库名\G
show create table 表名\G

查看表的信息:

show table status from 库名 like  '表名';

查看表中字段的信息:

show full columns from 表名;

 

---

 

设置字符集

创建时指定字符:

create database 库名 default character set=utf8; -- 建库时指定
create table 表名(字段) default character set=utf8; -- 建表时指定

修改全局字符集:

set character_set_connection=utf8; -- 建立连接使用的编码
set character_set_database=utf8; -- 数据库的编码
set character_set_results=utf8; -- 结果集的编码
set character_set_server=utf8; -- 数据库服务器的编码
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;

修改库的字符集:

alter database 库名 default character set=字符集;

修改表的字符集:

alter table 表名 convert to character set 字符集;

修改字段的字符集:

alter table 表名 modify 字段名 约束条件 character set gbk/utf8；