【Mysql】修改mysql的字符集和默认存储引擎，解决数据入库乱码问题

背景

在使用Python + Testlink做自动化的过程中，遇到了数据入库出现乱码的情况，后来通过修改字符集的方式解决了这个问题。下面的内容主要来自于一篇相关博文，博主做了细微的调整，

原文链接：https://blog.csdn.net/wyzxg/article/details/7581415

Mysql的字符集分类

字符集类型 描述 对应系统变量
客户端字符集 通知server端，客户端提交的sql语句编码格式 character_set_client
连接字符集 server端翻译sql语句时，使用的编码格式 character_set_connectiont
结果集字符集 server端返回结果集之前把结果集转换成的编码格式 character_set_results
存储字符集 数据在存储引擎里编码格式 character_set_results/character_set_server

为了不出现乱码，我们需要统一修改这些参数，比如我要把数据库的默认字符集改为utf8，可以在[client]下添加如下参数 default-character-set=utf8，在[mysqld]下添加如下参数default-character-set=utf8，然后重启mysql服务，再查看字符集（要重新登录客户端）

查看Mysql库现有字符集

  • mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | gbk                            |
| character_set_connection | gbk                            |
| character_set_database   | latin1                         |
| character_set_filesystem | binary                         |
| character_set_results    | gbk                            |
| character_set_server     | latin1                         |
| character_set_system     | utf8                           |
| character_sets_dir       | D:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
  • mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | gbk                            |
| character_set_connection | gbk                            |
| character_set_database   | latin1                         |
| character_set_filesystem | binary                         |
| character_set_results    | gbk                            |
| character_set_server     | latin1                         |
| character_set_system     | utf8                           |
| character_sets_dir       | D:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
  • mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci | 
| collation_database   | utf8_general_ci | 
| collation_server     | utf8_general_ci | 
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql的字符集分的比较细，可以采用默认值，也可以指定值

数据库字符集

创建使用默认字符集的库

  • mysql> create database db1;
    Query OK, 1 row affected (0.01 sec)
  • mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ | 
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

创建指定字符集的库

  • mysql> create database db2 default character set latin1;
    Query OK, 1 row affected (0.01 sec)

  • mysql> show create database db2;

+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */ | 
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

修改库的字符集

  • mysql> alter database db2 default character set utf8;
    Query OK, 1 row affected (0.00 sec)

  • mysql> show create database db2;

+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ | 
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

表字符集

  • mysql> use db2;
    Database changed

创建使用默认库字符集的表

  • mysql> create table t1(a varchar(10));
    Query OK, 0 rows affected (0.01 sec)
  • mysql> show create table t1;
+-------+-------------------------------------------------------+
| Table | Create Table                                          |
+-------+-------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+-------------------------------------------------------+
1 row in set (0.00 sec)

创建指定字符集的表

  • mysql> create table t2(a varchar(10)) default character set latin1;;
    Query OK, 0 rows affected (0.01 sec)
    ERROR:
    No query specified

  • mysql> show create table t2;

+-------+-------------------------------------------------------+
| Table | Create Table                                          |
+-------+-------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` varchar(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------+
1 row in set (0.00 sec)

更改表的字符集

  • mysql> alter table t2 default character set utf8;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0

  • mysql> show create table t2;

+-------+--------------------------------------------------------+
| Table | Create Table                                                             |
+-------+--------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` varchar(10) character set latin1 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+-------------------------------------------------------+
1 row in set (0.00 sec)

从上面可以看到，修改表的字符集时，不会影响已经存在列的字符集，但新增列将继承表的字符集，如下

  • mysql> alter table t2 add a2 varchar(10);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0

  • mysql> show create table t2;

+-------+---------------------------------------------+
| Table | Create Table                                |
+-------+---------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` varchar(10) character set latin1 default NULL,
  `a2` varchar(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+---------------------------------------------+
1 row in set (0.01 sec)

如果想更改已经存在表和字符列的字符集，要用如下方式

  • mysql> alter table t2 convert to character set latin1;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0

  • mysql> show create table t2;

+-------+--------------------------------------+
| Table | Create Table                         |
+-------+--------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` varchar(10) default NULL,
  `a2` varchar(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+--------------------------------------+
1 row in set (0.01 sec)

字段字符集

使用默认和指定字符集创建的列

  • mysql> create table t3 (a1 varchar(10),a2 varchar(10) character set latin1);
    Query OK, 0 rows affected (0.04 sec)

  • mysql> show create table t3;

+-------+-------------------------------------------------+
| Table | Create Table                                    |
+-------+-------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `a1` varchar(10) default NULL,
  `a2` varchar(10) character set latin1 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+-------------------------------------------------+
1 row in set (0.00 sec)

查看列的字符集

  • mysql> show full columns from t3;
+------+------------+------------------+-----+----+--------+------+--------------------------------+--------+
| Field| Type       | Collation        | Null| Key| Default| Extra| Privileges                     | Comment|
+------+------------+------------------+-----+----+--------+------+--------------------------------+--------+
| a1   | varchar(10)| utf8_general_ci  | YES |    | NULL   |      | select,insert,update,references|        | 
| a2   | varchar(10)| latin1_swedish_ci| YES |    | NULL   |      | select,insert,update,references|        | 
+------+------------+------------------+-----+----+--------+------+--------------------------------+--------+
2 rows in set (0.01 sec)

修改列的字符集

  • mysql> alter table t3 change a2 a2 varchar(10) character set utf8;
    Query OK, 0 rows affected (0.03 sec)
    Records: 0 Duplicates: 0 Warnings: 0

  • mysql> show full columns from t3;

+------+------------+----------------+-----+----+--------+------+--------------------------------+--------+
| Field| Type       | Collation      | Null| Key| Default| Extra| Privileges                     | Comment|
+------+------------+----------------+-----+----+--------+------+--------------------------------+--------+
| a1   | varchar(10)| utf8_general_ci| YES |    | NULL   |      | select,insert,update,references|        | 
| a2   | varchar(10)| utf8_general_ci| YES |    | NULL   |      | select,insert,update,references|        | 
+------+------------+----------------+-----+----+--------+------+--------------------------------+--------+
2 rows in set (0.00 sec)

结论：字符集从数据库、表、列的字符集是逐级继承的关系

命令总结

查看字符集命令

mysql> show create table t4; //显示表字符集
mysql> show create database db2; //显示库字符集
mysql> show full columns from t4; //显示列字符集

alter database db2 default character set utf8; //更改库的字符集
alter table t2 convert to character set latin1;//更改表的现存列字符集
alter table t2 default character set utf8;  //更能改表的默认字符集
alter table t3 change a2 a2 varchar(10) character set utf8; //更能改字段的字符集

修改mysql的默认存储引擎

  • mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM | 
+----------------+--------+
1 row in set (0.01 sec)

在[mysqld]下添加如下参数default-storage-engine=innodb

重启mysql服务，查看

  • mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB | 
+----------------+--------+
1 row in set (0.01 sec)

创建默认字符集的表

  • mysql> create table t4 (a1 int);
    Query OK, 0 rows affected (0.01 sec)

  • mysql> show create table t4;

+-------+-------------------------------------------+
| Table | Create Table                              |
+-------+-------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `a1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+-------------------------------------------+
1 row in set (0.00 sec)

更改表的默认字符集

  • mysql> alter table t4 engine myisam;
    Query OK, 0 rows affected (0.06 sec)
    Records: 0 Duplicates: 0 Warnings: 0

  • mysql> show create table t4;

+-------+-------------------------------------------+
| Table | Create Table                              |
+-------+-------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `a1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
+-------+-------------------------------------------+
1 row in set (0.00 sec)

------end------

posted @ 2018-05-15 23:11  Bingo-he  阅读(1358)  评论(0)  编辑  收藏  举报