一、查看字符集
1.查看MYSQL数据库服务器和数据库字符集
方法一：show variables like '%character%';
方法二：show variables like 'collation%';
2.查看MYSQL所支持的字符集
show charset;
3.查看库的字符集
语法：show database status from 库名 like 表名;
4.查看表的字符集
语法：show table status from 库名 like 表名;
mysql> show table status from class_7 like 'test_info';
5.查看表中所有列的字符集
语法：show full columns from 表名;
mysql> show full columns from test_info;
二、设置字符集
设置字符集一般有两种方法，一种是在创建表的时候设置字符集，另一种是表建成之后修改字符集。
1.创建时指定字符集
创建库的时候指定字符集：
语法：create database 库名 default character set=字符集；
create database db2 default character set=utf8
创建表的时候指定字符集：
语法：create table 表名(属性)default character set = 字符集；
mysql> create table test1(id int(6),name char(10)) default character set = 'gbk';
Query OK, 0 rows affected (0.39 sec)
2.修改字符集
修改全局字符集
/*建立连接使用的编码*/
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 database shiyan default character set gbk;
修改表的字符集
语法：alter table 表名 convert to character set 字符集;
alter table test1 convert to character set utf8;
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(6) DEFAULT NULL,
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk #原字符集
row in set (0.00 sec)
mysql> alter table test1 convert to character set utf8;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(6) DEFAULT NULL,
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 #修改后的字符集
row in set (0.00 sec)
修改列表的字符集
修改字段的字符集
语法：alter table 表名 modify 字段名 字段属性 character set gbk；
alter table test1 modify name char(10) character set gbk;
mysql> show full columns from test1;
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(6) | NULL | YES | | NULL | | select,insert,update,references | |
| name | char(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)
mysql> alter table test1 modify name char(10) character set gbk;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show full columns from test1;
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(6) | NULL | YES | | NULL | | select,insert,update,references | |
| name | char(10) | gbk_chinese_ci | YES | | NULL | | select,insert,update,references | |
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)
