修改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 在[mysqld]下添加如下參數 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> 作者 wyzxg
