[mysql] 常用命令五——编码

① 修改表的存储类型【存储引擎】

alter  table  表名   type=InnoDB;
alter  table  表名   type=MyISAM;

② 创建一个使用utf-8字符集的mydb2数据库

mysql> create database test character set utf8;
Query OK, 1 row affected (0.01 sec)

③ 创建一个使用utf-8字符集，并带校对规则的mydb3数据库。

mysql> create database mydb3 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

④ 查看前面创建的mydb2/mysql3数据库的编码

mysql> show create database mydb2;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mydb2    | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database mydb3;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mydb3    | CREATE DATABASE `mydb3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

⑤ 修改表的编码

alter table  表名 character set gbk collate gbk_chinese_ci;
alter table  表名 character set utf8 collate utf8_general_ci;

例子：把服务器中的数据库某一个库的字符集修改为gb2312;

mysql> alter database mydb2 character set gb2312;
Query OK, 1 row affected (0.00 sec)

mysql> show create database mydb2;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mydb2    | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET gb2312 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

⑥  插入和读取数据时的编码问题

 要想插入时不乱码(插入中文要告诉mysql客户采用gb2312编码)
 show variables like 'chara%';
 set character_set_client=gb2312;
 insert into employee(id,username) values('3','张三');
 
 要想查看时不乱码
 show variables like 'chara%';
 set character_set_results=gb2312;
 select * from employee;

在执行前，我们可以查看client和results的编码均为utf8
mysql> show variables like 'chara%';
| Variable_name            | Value
| character_set_client     | utf8
| character_set_connection | utf8
| character_set_database   | gbk
| character_set_filesystem | binary
| character_set_results    | utf8
| character_set_server     | gbk
| character_set_system     | utf8
| character_sets_dir       | D:\Program Files\MySQL\MySQL Server 5.5\share\charsets\

执行改变编码的命令：
mysql> set character_set_client=gb2312;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=gb2312;
Query OK, 0 rows affected (0.00 sec)

查看现在的编码：
character_set_client     | gb2312
character_set_connection | utf8
character_set_database   | gbk
character_set_filesystem | binary
character_set_results    | gb2312