查看和修改MySQL字符集的方法

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的字符集分的比较细，可以采用默认值，也可以指定值

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` |
+----------+--------------------------------------------------------------+
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平台

     windows下的mysql配置文件是my.ini

    一般在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;