(转)MySQL设置和修改编码问题

mysql版本：5.7.18

一、常用查看字符集设定命令

1、查看数据库支持的所有字符集。

show character set;
(或 show char set;)

2、查看当前状态命令（显示结果会包含数据库的字符集设置）

status(或 /s)

这里写图片描述
其中Db characterset对应的是数据库目录下的db.opt文件内容。（查到mysql安装目录下有4个db.opt文件，与显示相同的在mysql/data/mysql路径下）。

3、查看系统字符集设置（包括所有的字符集设置）

show variables like \'%char%\';

这里写图片描述
其中的含义如下：

条目	说明
character_set_client	为客户端使用的字符集。
character_set_connection	为连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型则按照服务器端默认的字符集设置。
character_set_database	为数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
character_set_results	为数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
character_set_server	为服务器安装时指定的默认字符集设定。
character_set_system	为数据库系统使用的字符集设定。

当客户端连接服务器的时候，它会将自己想要的字符集名称发送给mysql服务器，然后服务器就会使用这个字符集去设置 character_set_connection、character_set_client、character_set_results这三个值。

在cmd客户端使用GBK字符集，上图client、connection、results的值为GBK。
在Navicat for Mysql中运行命令，显示值为utf-8。
这里写图片描述

4、查看具体的数据库表的字符集设置

show full columns from tablename;
（或show create table tablename\G;
注：\G在cmd中可以去除打印的表格边框，在Navicat for Mysql中命令有\G会报错。）

5、查看数据库编码

show create database dbname;

二、创建时指定字符集

知道了怎么查找字符集的相关信息之后，我们就要懂得怎么在创建指定对象的时候，为该对象匹配相应的字符集。

1、服务器级

在安装MySQL时，可以设置服务器的默认编码格式，也可对 my.ini做修改，修改[mysqld]里面的character_set_server=utf8，则可设置character_set_server的值。

2、数据库级

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8;

注：如果不指定默认的字符集，则系统会根据character_set_database的值进行设置。
这里写图片描述

3、表级

CREATE TABLE  `db_name`.`tb_name` (id VARCHAR(20) NOT NULL,name VARCHAR(20) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在创建表时指定了默认字符集为utf8，即使character_set_database为gbk，表的列都为utf-8。
但要注意，如果没有定义表的默认字符集，则会按照character_set_database的值来设置。（试了一下应该是按照表所属的数据库字符集来设置吧）。

4、列级

CREATE TABLE  `db_name`.`tb_name` (   id varchar(20) NOT NULL,   name varchar(20) CHARACTER SET utf8 );

三、修改字符集命令

MySQL的字符集问题主要是两个概念，一个是Character Sets，一个是Collations，前者是字符内容
及编码，后者是对前者进行比较操作的一些规则。这两个参数集可以在数据库实例、单个数据库、表、列等四个级
别指定。

1、修改character_set_connection、character_set_client、character_set_results三值

SET NAMES charset_name [COLLATE \'collation_name\']
（例：SET NAMES utf8;）
该命令相当于
SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name;

另外，还可以修改配置文件my.ini，对[mysql]下增加 default-character-set=utf8，配置成你想要的字符集（个人尝试在my.ini里面配置过，没有成效，不知道是不是被使用的客户端想要的字符集给覆盖掉了呢？）

2、修改character_set_database字段

ALTER DATABASE db_name     [[DEFAULT] CHARACTER SET charset_name]     [[DEFAULT] COLLATE collation_name]
（例：alter database test_database character set latin2;）

3、修改character_set_server字段

最简单的方法是直接改my.ini配置文件里面[mysqld]的字段，增加character-set-server=gbk，然后重启mysqld，则可改为你想要的字符集。

4、修改表的字符集

ALTER TABLE tbl_name     [[DEFAULT] CHARACTER SET charset_name]     [COLLATE collation_name]
（例：ALTER table hahaha CHARACTER set gbk）

5、修改列的字符集

col_name {CHAR | VARCHAR | TEXT} (col_length)     [CHARACTER SET charset_name]     [COLLATE collation_name]
（例：ALTER TABLE t1 MODIFY     col1 VARCHAR(5)       CHARACTER SET latin1       COLLATE latin1_swedish_ci;）

四、附 Linux下MySQL字符集设置

MySQL的字符集问题主要是两个概念，一个是Character Sets，一个是Collations，前者是字符内容及编码，后者是对前者进行比较操作的一些规则。这两个参数集可以在数据库实例、单个数据库、表、列等四个级别指定。

对于使用者来说，一般推荐使用utf8编码来存储数据。而要解决乱码问题，不单单是MySQL数据的存储问题，还和用户的程序文件的编码方式、用户程序和MySQL数据库的连接方式都有关系。

首先，MySQL有默认的字符集，这个是安装的时候确定的，在编译MySQL的时候可以通过DEFAULT_CHARSET=utf8和DEFAULT_COLLATION=utf8_general_ci这两个参数（MySQL5.5版本，5.1版本用–with-charset=utf8 –with-collation=utf8_general_ci）来指定默认的字符集为utf8，这也是最一劳永逸的办法，这样指定后，客户端连接到数据库的编码方式也默认是utf8了，应用程序不需要任何处理。

但是遗憾的是，很多人编译安装MySQL的时候没有指定这两个参数，大多数人更是通过二进制程序的方式安装，那么这时候MySQL的默认字符集是latin1。而这时候我们仍然可以指定MySQL的默认字符集，通过my.cnf文件增加两个参数：

在[mysqld]下添加default-character-set=utf8（mysql 5.5 版本添加character-set-server=utf8）；
在[client]下添加default-character-set=utf8。

这样我们建数据库建表的时候就不用特别指定utf8的字符集了。配置文件里的这种写法解决了数据存储和比较的问题，但是对客户端的连接是没有作用的，客户端这时候一般需要指定utf8方式连接才能避免乱码。也就是传说总的set names命令。事实上，set names utf8命令对应的是服务器端以下几个命令：

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

但这三个参数是不能写在配置文件my.cnf里的。只能通过set命令来动态修改。我们需要的是在配置文件里写好一劳永逸的办法。那么这时候，是否有在服务端解决问题的办法呢，可行的思路是在init_connect里设置。这个命令在每个普通用户连接上来的时候都会触发执行，可以在[mysqld]部分增加以下一行设置连接字符集：

在[mysqld]下添加：
　　init_connect = \'SET NAMES utf8\'

总结：
1、首选在编译安装MySQL的时候指定两个参数使用utf8编码。
2、次选在配置文件my.cnf或my.ini设定两个参数，同时设置init_connect参数。
3、第三在配置文件my.cnf或my.ini设定两个参数，同时客户端的连接指定set names命令。
4、在配置文件my.cnf里的client和server处加入default-character-set参数方便管理。

秒客网