Mysql的字符集分类字符集类型描述和修改

Mysql的字符集分类字符集类型描述对应系统变量客户端字符集通知server端,客户端提交的sql语句编码格式character_set_client连接字符集server端翻译sql语句时,使用的编码格式character_set_connectiont结果集字符集server端返回结果集之前把结果集转换成的编码格式character_set_results存储字符集数据在存储引擎里编码格式character_set_results/character_set_server为了不出现乱码,我们需要统一修改这些参数,比如我要把数据库的默认字符集改为utf8,可以在[client]下添加如下参数 default-character-set=utf8,在[mysqld]下添加如下参数default-character-set=utf8,然后重启mysql服务,再查看字符集(要重新登录客户端)查看Mysql库现有字符集
mysql> show variables like ‘character%’;
| Variable_name | Value |
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\xampp\mysql\share\charsets\ |
8 rows in set (0.00 sec)

mysql> show variables like ‘character%’;
**| Variable_name | Value |
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\xampp\mysql\share\charsets\ |
8 rows in set (0.00 sec)

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

表字符集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> 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> 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)

字段字符集使用默认和指定字符集创建的列
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> 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> 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; //更能改字段的字符集

修改mysql的默认存储引擎
mysql> show variables like ‘storage%’;
| Variable_name | Value |
| storage_engine | MyISAM |
1 row in set (0.01 sec)

在[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> 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)------end-----

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页