MySQL数据库编码概要_MySQL

bitsCN.com

大家在使用数据库的时候，总会出现各种各样的编码问题，看了MySQL官方文档后，记录下一些MySQL的编码体系知识，如MySQL有那几层使用编码的地方，MySQL客户端和服务端交互时哪些环节涉及到的编码，和如何指定编码。

基本概念：

mysql数据库编码层次：系统层，server层，database层，table层，column层，还有client，connection和result三种和客户端通讯相关的场景；A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set；To connect to MySQL from Java you have to use the JDBC driver from MySQL. The MySQL JDBC driver is called MySQL Connector/J.MySQL 4.1以下对unicode支持不好jdbc3.0.16及以上才支持使用数据库本身编码，否则使用ISO8859-1在mysql控制台下输入show variables like 'character_set_%'; 查看当前编码相关系统变量，后面会解析其中几项

     mysql> SHOW VARIABLES LIKE 'character%';+--------------------------+---------------------------------+| Variable_name            | Value                           |+--------------------------+---------------------------------+| character_set_client     | latin1                          || character_set_connection | latin1                          || character_set_database   | latin1                          || character_set_filesystem | binary                          || character_set_results    | latin1                          || character_set_server     | latin1                          || character_set_system     | utf8                            || character_sets_dir       | D:"mysql-5.0.37"share"charsets" |

mysql的多层字符编码支持

1.server层

作用
整个数据库服务器默认编码。
配置点
通过系统变量character_set_server参数指定server层编码
可以在mysqld执行时加入该参数
或者在编辑mysql时候，设置该参数

2.database层

作用
数据库级别默认编码。
配置点
通过系统变量character_set_database参数指定database层编码
建表时候指定编码

3.table层

同理，table层的编码设置仅影响当前表的所有未指定编码的列的编码。但这个指定是mysql独有的，而且只能通过sql在建表或修改表时指定， 在标准sql中，没有可指定表编码的sql语法

4.column层

作用
设置列的编码。
配置点
建表或修改列时设置。这是标准sql语法。

mysql server与client交互时编码如何转换

1.客户端发送语句

character set and collation system variables are involved in handling traffic for the connection between a client and the server. Every client has connection-related character set and collation system variables.
The server takes the character_set_client system variable to be the character set in which statements are sent by the client.
在客户端和服务端通讯时，会涉及到另外几个编码设置相关的系统变量的，每个客户端都有属于自己的编码链接相关编码。
服务端使用系统变量character_set_client来处理客户端发来的语句。

2.服务端处理语句

The server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8)
服务端会把客户端发来的语句（以character_set_client 编码）转换为character_set_connection编码。

A character string literal may have an optional character set introducer and COLLATE clause [_charset_name]'string' [COLLATE collation_name]
如：SELECT _latin1'string' COLLATE latin1_danish_ci;
在缺少编码指定是，默认会使用character_set_connection指定的编码。
The character set used for literals that do not have a character set introducer and for number-to-string conversion.
没有前导编码修饰(introducer)的文本和数字到字符的转换会应用character_set_connection编码。

For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.
表的列字段与客户端传来的语句进行比较时，会把客户端语句转成列对应编码再进行比较，这是因为列字段拥有更高优先级。

3.服务端返回内容

The character_set_results system variable indicates the character set in which the server returns query results to the client
系统变量character_set_results用来把数据以该编码方式返回给客户端。

下面用一张图来大致描述下上面的内容（个人理解所画）

服务端如何自动判断并设置编码？

The character encoding between client and server is automatically detected upon connection. You specify the encoding on the server using the character_set_server for server versions 4.1.0 and newer, and character_set system variable for server versions older than 4.1.0. The driver automatically uses the encoding specified by the server.
如果客户端连接时没有提供编码(连接串无characterEncoding)，则服务端会使用character_set_server变量来作为客户端编码（4.1.0后）。

For example, to use 4-byte UTF-8 character sets with Connector/J, configure the MySQL server with character_set_server=utf8mb4, and leave characterEncoding out of the Connector/J connection string. Connector/J will then autodetect the UTF-8 setting.

客户端如何制定编码？
To override the automatically detected encoding on the client side, use the characterEncoding property in the URL used to connect to the server.

When a client connects to the server, it sends the name of the character set that it wants to use. The server uses the name to set the character_set_client, character_set_results, and character_set_connection system variables. In effect, the server performs a SET NAMES operation using the character set name.
客户端与服务端建立链接时，会发送客户端所希望使用的编码集。服务端会用这个编码集去初始化三个系统变量character_set_client, character_set_results, and character_set_connection。如执行了语句 SET NAMES XXX一般:

SET NAMES xx可以指定connection编码为xx:character_set_connection,character_set_results,character_set_client 系统变量可修改；

SET NAMES 'charset_name' 这句SQL等同与执行下面3个语句:

SET character_set_client = charset_name;SET character_set_results = charset_name;SET character_set_connection = charset_name;

A SET CHARACTER SET charset_name 等同于执行下面3个语句:

SET character_set_client = charset_name;SET character_set_results = charset_name;SET collation_connection = @@collation_database;

参考

http://dev.mysql.com/doc/refman/5.5/en/charset.html

bitsCN.com