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
0
广告
281
收藏
