查看和修改MySQL字符集的方式_互传的数据恢复得
日期：2014-07-13 / 人气： / 来源：网络
MySQL字符集多种多样，下面为列举了其中三种最常见的字符集查看方法，该方法供您参考，希望对学习MySQL数据库能有所启迪。
一、查看MySQL数据库服务器和数据库MySQL字符集。
mysql> show variables like '%char%';
-------------------------- ------------------------------------- ------
| 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 | D:MySQL Server 5.0sharecharsets |......
-------------------------- ------------------------------------- ------
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> 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的字符集分的比较细，可以采用默认值，也可以指定值
使用默认字符集的库
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
---------- --------------------------------------------------------------
| Database | Create Database |
---------- --------------------------------------------------------------
| db1 | CREATE DATABASE `db1` |
---------- --------------------------------------------------------------
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` |
---------- ----------------------------------------------------------------
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` |
---------- --------------------------------------------------------------
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>
二、查看MySQL数据表（table）的MySQL字符集。mysql> show table status from sqlstudy_db like '%countries%';
----------- -------- --------- ------------ ------ ----------------- ------
| Name | Engine | Version | Row_format | Rows | Collation |......
----------- -------- --------- ------------ ------ ----------------- ------
| countries | InnoDB | 10 | Compact | 11 | utf8_general_ci |......
----------- -------- --------- ------------ ------ ----------------- ------
三、查看MySQL数据列（column）的MySQL字符集。
mysql> show full columns from countries;
---------------------- ------------- ----------------- --------
| Field | Type | Collation | .......
---------------------- ------------- ----------------- --------
| countries_id | int(11) | NULL | .......
| countries_name | varchar(64) | utf8_general_ci | .......
| countries_iso_code_2 | char(2) | utf8_general_ci | .......
| countries_iso_code_3 | char(3) | utf8_general_ci | .......
| address_format_id | int(11) | NULL | .......
---------------------- ------------- ----------------- --------
MySQL修改默认字符集修改mysql的默认字符集是通过修改它的配置文件来实现的。一般分两种情况：
方法一:
Windows平台
一般在C:Program FilesMySQLMySQL Server 5.0
default-character-set=gbk #或gb2312,big5,utf8
然后重新启动mysql:运行->services.msc->停止MySql->启动 OK
或
/etc/init.d/mysql restart
或用其他方法重新启动，就生效了。
Unix平台
linux下的mysql配置文件是my.cnf
一般是/etc/my.cnf，如果找不到可以用find命令找一下：
find / -iname my.cnf
在这个文件里面加上
default-character-set=gbk #或gb2312,big5,utf8
然后重新启动mysql
net stop mysql
net start mysql
方法二:
通过MySQL命令行修改:
mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_system=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_connection=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_database=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_server=utf8;
Query OK, 0 rows affected (0.01 sec)
查看:
mysql> show variables like 'character_set_%';
utf8 改为 gb2312
重启mysql :
如何启动/停止/重启MySQL
启动、停止、重启 MySQL 是每个拥有独立主机的站长必须要撑握的操作，下面为大家简单介绍一下操作方法：
一、启动方式
1、使用 service 启动：service mysqld start
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld start
3、使用 safe_mysqld 启动：safe_mysqld&
二、停止
1、使用 service 启动：service mysqld stop
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld stop
3、mysqladmin shutdown
三、重启
1、使用 service 启动：service mysqld restart
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld restart
终极方法
mysql> set names gbk;MySQL5.5数据库主从复制配置图解
导读：MySQL5.5 主从复制操作系统环境:CentOS5.51.MySQL 主从流程图2.MySQL 主从说明 a.主服务器一定要开启二进制日志文件. b.MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出STARTSLAVE
MySQL数据库,
作者：管理员
推荐内容 Recommended
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19
相关内容 Related
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19