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

叁歲伎倆 2022-06-12 12:14 231阅读 0赞

author:skate  
time:2012/05/18

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

1.修改mysql的字符集

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

在\[mysqld\]下添加如下参数  
default-character-set=utf8

然后重启mysql服务，再查看字符集（要重新登录客户端）

mysql>

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                                              |  
\+----------+--------------------------------------------------------------+  
| 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                                                                                                 |  
\+-------+--------------------------------------------------------------------------------------------------------------+  
| 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)

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> 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>

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

查看字符集命令  
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; //更能改字段的字符集

2.修改mysql的默认存储引擎

mysql>

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

重启mysql服务，查看

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;  
\+-------+--------------------------------------------------------------------------------------+  
| 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>

\------end------