怎么修改mysql的字符集和默认存储引擎

这篇文章主要讲解了“怎么修改mysql的字符集和默认存储引擎”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么修改mysql的字符集和默认存储引擎”吧！

修改mysql的字符集和默认存储引擎
 
1.修改mysql的字符集
mysql库现有字符集
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | latin1                     | 
| character_set_filesystem | binary                     | 
| character_set_results    | latin1                     | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.00 sec)  www.2cto.com  
mysql>
mysql的字符集分为几类
A.客户端字符集：通过系统变量”character_set_client“表示，通知server端，客户端提交的sql语句编码格式
B.连接字符集：通过系统变量”character_set_connectiont“表示，server端翻译sql语句时，使用的编码格式
C.结果集字符集：通过系统变量”character_set_results“表示，server端返回结果集之前把结果集转换成的编码格式
D.存储字符集：通过系统变量”character_set_results“和“character_set_server”表示，是数据在存储引擎里编码格式
 
为了不出现乱码，我们需要统一修改这些参数，比如我要把的默认字符集改为utf8，如下所示
在[client]下添加如下参数
default-character-set=utf8
在[d]下添加如下参数
default-character-set=utf8
然后重启mysql服务，再查看字符集（要重新登录客户端）
  www.2cto.com  
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       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql>
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的字符集分的比较细，可以采用默认值，也可以指定值
 
A.数据库字符集
使用默认字符集的库
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      www.2cto.com                                            |
+----------+--------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ | 
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
 
B.表字符集
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     www.2cto.com                                                                                                |
+-------+--------------------------------------------------------------------------------------------------------------+
| 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>
从上面可以看到，修改表的字符集时，不会影响已经存在列的字符集，但新增列将继承表的字符集，如下
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>
 
如果想更改已经存在表和字符列的字符集，要用如下
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)
 
C.字段字符集
 
使用默认和指定字符集创建的列
mysql> create table t3 (a1 varchar(10),a2 varchar(10) character set latin1);
Query OK, 0 rows affected (0.04 sec)  www.2cto.com  
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>
 
查看列的字符集
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>
 
结论：字符集从数据库、表、列的字符集是逐级继承的关系
 
查看字符集命令
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; //更能改字段的字符集
   www.2cto.com  
2.修改mysql的默认存储引擎
mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM | 
+----------------+--------+
1 row in set (0.01 sec)
mysql>
 
在[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>
 
创建默认字符集的表
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;
+-------+--------------------------------------------------------------------------------------+  www.2cto.com  
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `a1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
 

感谢各位的阅读，以上就是“怎么修改mysql的字符集和默认存储引擎”的内容了，经过本文的学习后，相信大家对怎么修改mysql的字符集和默认存储引擎这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！