MySQL字符集转化过程

最新推荐文章于 2024-07-22 16:28:14 发布

莫特@

最新推荐文章于 2024-07-22 16:28:14 发布

阅读量1.1k

点赞数

分类专栏： Mysql

Mysql 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

MySQL字符集设置

1、系统变量：

– character_set_server：默认的内部操作字符集

– character_set_client：客户端来源数据使用的字符集

– character_set_connection：连接层字符集

– character_set_results：查询结果字符集

– character_set_database：当前选中数据库的默认字符集

– character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem，默认binary是不做任何转换的

– character_set_system：这个值总是utf8，不需要设置，是为存储系统元数据的字符集

– 还有以collation_开头的同上面对应的变量，用来描述字符序。

在[client]下添加如下参数，影响系统变量character_set_client和character_set_connection和character_set_results
default-character-set=utf8

在[mysqld]下添加如下参数，影响系统变量character_set_server和character_set_database
default-character-set=utf8

如下三个系统变量不需要关心，不会影响乱码
character_set_filesystem
character_set_system
character_sets_dir

2、MySQL的字符集支持(Character Set Support)有两个方面：

　　1.字符集(Character set)和排序方式(Collation)。
　　对于字符集的支持细化到四个层次:

　　服务器(server)，数据库(database)，数据表(table)和连接(connection)。

　　MySQL对于字符集的指定可以细化到一个数据库，一张表，一列，应该用什么字符集。

　　但是，传统的程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配置，那么，默认的配置从何而来呢?

　　(1)编译MySQL 时，指定了一个默认的字符集，这个字符集是 latin1;
　　(2)安装MySQL 时，可以在配置文件 (my.cnf) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的;
　　(3)启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时character_set_server被设定为这个默认的字符集;
　　(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server;
　　(5)当选定了一个数据库时，character_set_database被设定为这个数据库默认的字符集;
　　(6)在这个数据库里创建一张表时，表默认的字符集被设定为character_set_database，也就是这个数据库默认的字符集;
　　(7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集;

　　简单的总结一下，如果什么地方都不修改，那么所有的数据库的所有表的所有栏位的都用 latin1 存储，不过我们如果安装 MySQL，一般都会选择多语言支持，也就是说，安装程序会自动在配置文件中把default_character_set设置为 UTF-8，这保证了缺省情况下，所有的数据库的所有表的所有栏位的都用 UTF-8 存储。

2.查看默认字符集(默认情况下，mysql的字符集是latin1(ISO_8859_1)通常，查看系统的字符集和排序方式的设定可以通过下面的两条命令：

从上例中我们可以看出字符集utf8，其中默认的排序方式为utf8_general_ci。
排序方式的命名规则为：字符集名字_语言_后缀，其中各个典型后缀的含义如下：
1）_ci：不区分大小写的排序方式
2）_cs：区分大小写的排序方式
3）_bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此_bin的排序方式不包含人类语言

collation_connection:在配置文件里调整[client]default-character-set就可以控制这个参数
collation_server：配置文件里调整[mysqld]collation_server=utf8_general_ci,一定要和[mysqld]default-character-set使用相同字符集
collation_database:在配置文件里调整[mysqld]default-character-set就可以控制这个参数

3、MySQL中的字符集转换过程

(1). MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；

(2). 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：

使用每个数据字段的CHARACTER SET设定值；

若上述值不存在，则使用对应数据表的DEFAULT CHARACTERSET设定值(MySQL扩展，非SQL标准)；

若上述值不存在，则使用对应数据库的DEFAULT CHARACTERSET设定值；

若上述值不存在，则使用character_set_server设定值。

(3). 将操作结果从内部操作字符集转换为character_set_results。

另一种解释：

1、打开命令提示符窗口，命令提示符窗口自身存在某一种字符集，该字符集的查看方法是：在命令提示符窗口的标题栏上右键单击，选择“默认值-选项-默认代码页即可以设置当前命令提示符窗口的字符集。
2、在命令提示符窗口中输入MySQL命令或sql语句，回车后，这些MySQL命令或sql语句由“命令提示符窗口字符集”转换为“character_set_client”定义的字符集
3、使用命令提示符窗口成功连接MySQL服务器后，就建立了一条“数据通信链路”，MySQL命令或sql语句沿着“数据链路”传向MySQL服务器，由“character_set_client”定义的字符集转换为character_set_connection定义字符集
4、 MySQL服务实例收到数据通信链路中的MySQL命令或sql语句，将MySQL语句或sql语句从character_set_connection定义的字符集转换为character_set_server定义的字符集
5、若MySQL命令或sql语句针对于某个数据库进行操作，此时将MySQL命令或sql命令从character_set_server定义的字符集转换为character_set_database定义的字符集
6、 MySQL命令或sql语句执行结束后，将执行结果设置为character_set_results定义字符集
7、执行结果沿着打开的数据通信链路原路返回，将执行结果又character_set_results定义的字符集转character_set_client定义的字符集，最终转换为命令提示符窗口字符集显示到命令提示符窗口中。

4、常见问题解析

向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集，查询时设置连接字符集为utf8

– 插入时根据MySQL服务器的默认设置，character_set_client、character_set_connection和character_set_results均为latin1；

– 插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存；

– 查询时的结果将经过utf8=>utf8的字符集转换过程，将保存的6个字节原封不动返回，产生乱码……

向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8

– 插入时根据连接字符集设置，character_set_client、character_set_connection和character_set_results均为utf8；

– 插入数据将经过utf8=>utf8=>latin1的字符集转换，若原始数据中含有\u0000~\u00ff范围以外的Unicode字符，会因为无法在latin1字符集中表示而被转换为“?”(0×3F)符号，以后查询时不管连接字符集设置如何都无法恢复其内容了。

5、检测字符集问题的一些手段

SHOW CHARACTER SET;

SHOW COLLATION;

SHOW VARIABLES LIKE ‘character%’;

SHOW VARIABLES LIKE ‘collation%’;

SQL函数HEX、LENGTH、CHAR_LENGTH

SQL函数CHARSET、COLLATION

6、使用MySQL字符集时的建议

建立数据库/表和进行数据库操作时尽量显式指出使用的字符集，而不是依赖于MySQL的默认设置，否则MySQL升级时可能带来很大困扰；

数据库和连接字符集都使用latin1时虽然大部分情况下都可以解决乱码问题，但缺点是无法以字符为单位来进行SQL操作，一般情况下将数据库和连接字符集都置为utf8是较好的选择；

使用mysql C API时，初始化数据库句柄后马上用mysql_options设定MYSQL_SET_CHARSET_NAME属性为utf8，这样就不用显式地用 SET NAMES语句指定连接字符集，且用mysql_ping重连断开的长连接时也会把连接字符集重置为utf8；

对于mysql PHP API，一般页面级的PHP程序总运行时间较短，在连接到数据库以后显式用SET NAMES语句设置一次连接字符集即可；但当使用长连接时，请注意保持连接通畅并在断开重连后用SET NAMES语句显式重置连接字符集。

7、其他注意事项

my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集，不会对使用libmysqlclient库的应用程序产生任何作用！

对字段进行的SQL函数操作通常都是以内部操作字符集进行的，不受连接字符集设置的影响。

SQL语句中的裸字符串会受到连接字符集或introducer设置的影响，对于比较之类的操作可能产生完全不同的结果，需要小心！

三、修改mysql默认字符集

下面介绍下几个MYSQL命令：
1.show character set;或show char set;
查看数据库支持的所有字符集
2.status;或\s;
查看当前状态里面包括当然的字符集设置
3.show variables like 'char%';
查看系统字符集设置，包括所有的字符集设置
4.show table status from sqlstudy like '%countries%';
查看sqlstudy数据库中表的字符集设置
5.show full columns from countries;
查看表列的字符集设置，关键是在同一个表中，每列可以设置成不同的字符集
知道怎么查看字符集了，下面我来说下如何设置这些字符集（当然全是我这几天从网上整理的，呵呵）
1.修改服务器级
a. 临时更改：
mysql>SET GLOBAL character_set_server=utf8;
b. 永久更改：
修改my.cnf文件
[mysqld]
default-character-set=utf8
2.修改数据库级
a. 临时更改：
mysql>SET GLOBAL character_set_database=utf8;
b. 永久更改：
改了服务器级就可以了
3.修改表级
mysql>ALTER TABLE table_name DEFAULT CHARSET utf8;
更改了后永久生效
4.修改列级
修改示例：
mysql>alter
table `products` change `products_model` `products_model` varchar( 20 )
character set utf8 collate utf8_general_ci null default null;
更改了后永久生效
5.更改连接字符集
a. 临时更改：
mysql> set names utf8;
b. 永久更改：
修改my.cnf文件
在[client]中增加：
default-character-set=utf8
执行SQL语句时信息的路径是这样的
信息输入路径：client→connection→server;
信息输出路径：server→connection→results.

四、mysql字符集转换实例：

以原来的字符集为latin1为例，升级成为utf8的字符集。原来的表: databasename (default charset=latin1)，新表：new_databasename(default charset=utf8)。

mysql> show create database databasename;
+--------------+-------------------------------------------------------------------------+
| Database | Create Database |
+--------------+-------------------------------------------------------------------------+
| databasename | CREATE DATABASE `databasename` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+--------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

背景：某个系统的mysql数据库dnname采用默认的latin1字符集，系统升级需要将所有数据转换成utf-8格式，目标数据库为newdbname(建库时使用utf8)

方法一：

1、命令行执行：mysqldump --opt -hlocalhost -uroot -p*** --default-character-set=lantin1 dbname > /usr/local/dbname.sql

2、将 dbname.sql文件中的create table语句的CHARSET=latin1改为CHARSET=utf8

3、在dbname.sql文件中的insert语句之前加一条'set names utf8;'

4、将dbname.sql转码为utf-8格式，建议使用UltraEditor，可以直接使用该编辑器的'转换->ASCII到UTF-8(Unicode编辑)'，或者将文件另存为UTF-8(无BOM)格式

5、创建新数据库
CREATE DATABASE new_dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

6、命令行执行：mysql -hlocalhost -uroot -p*** --default-character-set=utf8 new_dbname < /usr/local/dbname.sql

总结：这种方法有个致命之处就是当数据中有大量中文字符和其他特殊符号字符时，很有可能导致在[步骤五]时报错导致无法正常导入数据，如果数据库比较大可以分别对每张表执行上述步骤

方法二（推荐大家使用）：

为了解决第一种方法中总结时说到的问题，在网上苦苦查找了一天资料才东拼西凑的搞出一个比较稳妥的解决方法

1、将待导出的数据表的表结构导出（可以用Phpmyadmin、mysqldump等，很简单就不说了），然后将导出的create table语句的CHARSET=latin1改为CHARSET=utf8，在目标库newdbname中执行该create table语句把表结构建好，接下来开始导出-导入数据

2、命令行：进入mysql命令行下，mysql -hlocalhost -uroot -p*** dbname

3、执行SQL select * from tbname into outfile '/usr/local/tbname.sql';

4、将tbname.sql转码为utf-8格式，建议使用UltraEditor，可以直接使用该编辑器的'转换->ASCII到UTF-8(Unicode编辑)'，或者将文件另存为UTF-8(无BOM)格式

5、在mysql命令行下执行语句 set character_set_database=utf8; 注：设置mysql的环境变量，这样mysql在下一步读取sql文件时将以utf8的形式去解释该文件内容

6、在mysql命令行下执行语句 load data infile 'tbname.sql' into table newdbname.tbname;

注意：千万不要忘了第四步

采用第二种方法，所有数据均正常导入，且格式转换成功没有乱码，要注意最好是源字符的超级，或者确定比源字符集的字库更大。

莫特@

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL字符集转化过程

MySQL字符集设置1、系统变量：– character_set_server：默认的内部操作字符集– character_set_client：客户端来源数据使用的字符集– character_set_connection：连接层字符集– character_set_results：查询结果字符集– character_set_datab
复制链接

扫一扫

专栏目录