您好,欢迎访问一九零五行业门户网

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

bitscn.com 修改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 specifiedmysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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>   作者 wyzxg bitscn.com
其它类似信息

推荐信息