Mysql-字符集实战

概述

最近在并行学习mysql，字符集其实是很多初学者都理不清的问题，正好最近学到这部分，借这个机会跟大家一起整理一下

一、Mysql数据库字符集

1.1 查看字符集相关

查看mysql当前字符集

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       | /mysql/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.00 sec)

• character_set_client：客户端请求数据的字符集
• character_set_connection：客户机/服务器连接的字符集
• character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。
• character_set_filesystem：文件系统字符集，把os上文件名转化成此字符集，即把 character_set_client 转换 character_set_filesystem， 默认binary是不做任何转换的
• character_set_results：结果集，返回给客户端的字符集
• character_set_server：数据库服务器的默认字符集
• character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。

校验规则（collation）是在字符集内用于比较字符的一套规则，即字符集的排序规则
查看字符集对应支持的校验规则

mysql>show character set;

mysql> show variables like 'collation%'; --查看当前校验规则
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

1.2 设置客户端字符集

更改客户端字符集，也就是更改了客户端的字符集参数，更改后不需要重启mysql
客户端字符集参数：
character_set_client,character_set_connection,character_set_results 三个的字符参数集合系统的字符集是一致

• 临时设置

1. 方法一：
   打开mysql的时候通过指定字符集–default-character-set

mysql -uroot -p123456 -S /data/3306/mysql.sock --default-character-set=utf8
--仅对当次登录生效

2. 方法二：
   通过set names utf8 临时设置客户端字符集

set names utf8

• 永久设置

1. my.cnf中配置default-character-set参数

$ vi my.cnf 
[client]
default-character-set=utf8

1.3 设置服务器字符集

• 配置my.cnf文件来永久设置mysql服务器字符集

[mysqld]
default-character-set=utf8 --5.1版本使用
character-set-server=utf8 --其他版本使用

这里只会更改character_set_server的值，已创建过的数据库字符集character_set_database不会更改

1.4 设置数据字符集

设置数据库字符集就是对character_set_database的更改。
character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。

• 方法一
  更改当前使用数据库的character_set_database

alter database character set utf8;
or
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name

• 方法二
  建库时指定默认字符集参数

create database xxx default character set  utf8 collate utf8-general-ci

注：下面会讲如何更改已存有数据的数据库的字符集。

1.5 设置表字符集

表的字符集如果后期更改设置导入导出

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name

二、MySQL字符集相关问题处理

2.1 字符集乱码处理

处理乱码，就一句话：把客户端和服务端的字符集统一！

• 查看当前字符集

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | utf8                                | -客户端字符集
| character_set_connection | utf8                                | -连接字符集
| character_set_database   | latin1                              | -数据库字符集
| character_set_filesystem | binary                              | -文件系统字符集
| character_set_results    | utf8                                | -返回结果字符集
| character_set_server     | latin1                              | -服务器字符集
| character_set_system     | utf8                                | -系统字符集
| character_sets_dir       | /mysql/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------+

• 在my.cnf中设置统一的客户端和服务器字符集

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8 --5.1版本参数
character-set-server=utf8 --5.1以上版本使用

• 重启mysql

 /data/3306/mysql restart

[root@study 3306]# netstat -lntup|grep 330 --检查是否mysql启动成功
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      87761/mysqld        
tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      89764/mysqld        
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      104482/mysqld       
 
 mysql -uroot -p123456 -S /data/3306/mysql.sock -打开mysql

• 检查是否修改正确

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------+
| 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       | /mysql/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.01 sec)

2.2 更改数据库的字符集正确步骤

1） 导出表结构
导出-default-character-set=latin1 设置旧的字符集，统一字符集

mysqldump -uroot -p -default-character-set=latin1 -d dbname >table.sql

2） 编辑table.sql，将latin1替换成utf8

3）停业务导出表数据

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 -S /data/3306/mysql.sock dbname> data.sql

• –quick 用于转储大的表
• –no-create-info 不创建create table语句
• –extended-insert 包括几个values多行insert，文件更小
• –default-character-set 按照原有字符集导出，就不会出乱码

4）编辑修改data.sql 将set names latin1 改成set names utf8；

5）建库指定 utf8

create database test default charset utf8;

6）创建表，导入表结构

mysql -uroot -p dbname <table.sql

7）导入数据

mysql -uroot -p dbname <data.sql

总结

其实字符集问题，在前期部署规划的时候就应该跟应用开发人员协调好，形成规范；程序使用字符集，客户端字符集，服务器字符集三集保持一致。