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

author:skate

time:2012/05/18

修改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)

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

+----------+--------------------------------------------------------------+

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

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;

+-------+--------------------------------------------------------------------------------------+

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