10.4. Connection Character Sets and Collations

Several character set and collation system variables relate to a client's interaction with the server. Some of these have been mentioned in earlier sections:
一些字符集和collation，是用户client's对Server的相互作用。有些在前面已经提及了：

The server character set and collation can be determined from
the values of the character_set_server and
collation_server system variables.
服务器(server)的字符集和排序(collation)是被 character_set_server和 collation_server这两个系统变量system variables的值決定

The character set and collation of the default database can be
determined from the values of the
character_set_database and
collation_database system variables.
数据库的默认(default) 字符集(character se)t和 排序(collation) 是被 character_set_database和 collation_database这两个系统变量system variables的值決定

Additional 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. ¯`'•.¸(•¿•).•:*¨¨*:•.(°_°)~*~get it ??? ~*~(°_°).•:*¨¨*:•.(•¿•)¸.•'´¯

Consider what a “connection” is: It's what you make
when you connect to the server. The client sends SQL statements,
such as queries, over the connection to the server. The server
sends responses, such as result sets, over the connection back to
the client.
This leads to several questions about character set
and collation handling for client connections, each of which can
be answered in terms of system variables:
想想"连接connection"是什么：是你连到服务器Server时作的事情。客户端Client通过这条连接发送SQL语句(statements)，
比如查询(queries)，通过 connection到server, 服务器则送回回应,像是结果集合 result sets, 通过连接给客户端client，
这导致
了客户端连接client connections处理字符集和collation的一些问题，它们每个都可以按照系统变量system variables来回答：

What character set is the statement in when it leaves the
client?
当查询 离开客户端时 是什么字符集？
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 这个变量variable, 来 作为客户端发送查询所用的字符集

What character set should the server translate a statement to
after receiving it?
服务器端在接收到了查询以后, 应该把翻译translate到什么字符集里？
For this, 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_connection 和 collation_connection 这两个变量variables。
它把客户端送来的查询 从 character_set_client转换成
character_set_connection（除了latin1或者utf8 的字符串）collation_connection is important for
comparisons of literal strings. For comparisons of strings
with column values, collation_connection
does not matter because columns have their own collation,
which has a higher collation precedence. collation_connection 对于比较字符串literal strings非常
重要，对于 列columns值比较字符串是没有关系does not matter，因为列columns有自己的collation(拥有更高优先)

What character set should the server translate to before
shipping result sets or error messages back to the client?
服务器要送回结果result集合或者错误信息给客户端时 应该用什么字符集？
The character_set_results system variable
indicates the character set in which the server returns query
results to the client. This includes result data such as
column values, and result metadata such as column names. character_set_results变量指示了送回给客户端 结果result集合的字符集，这包括了列column值，或者列column名 等结果数据result data

You can fine-tune the settings for these variables, or you can
depend on the defaults (in which case, you can skip the rest of
this section).
你可以调整这些变量的值，或者就使用默认的（那样 你就可以跳過以下部分）

There are two statements that affect the connection character
sets:
有两个语句statements作用(affect)连接字符集设置：

SET NAMES indicates what character set the
client will use to send SQL statements to the server.
Thus,
SET NAMES 'cp1251' tells the server
“future incoming messages from this client are in character
set cp1251.”
It also specifies the
character set that the server should use for sending results back
to the client. (For example, it indicates what character set to
use for column values if you use a SELECT
statement.)SET NAMES 指示:客户端送到server的SQL语句里是什么character set。
因此，SET NAMES 'cp1251' 就告诉server "将來future incoming的信息, 从这个客户端送来的信息将是使用'cp1251'这个字符集。
这也指定了server送回的结果所用的字符集，（例如 如果你用了一个select语句 它会指出列值column values
拥有的字符集）

A SET NAMES 'x'
statement is equivalent to these three statements:
SET NAMES 'x'语句相当于下面三个语句：

Setting character_set_connection to
x also sets
collation_connection to the default collation
for x.

SET CHARACTER SET is similar to SET
NAMES but sets the connection character set and
collation to be those of the default database.
但connection character set and collation 是转换成默认值, 当然是 瑞典文 latin1_swedish_ci
A SET
CHARACTER SET x statement is equivalent to these three statements: SET
CHARACTER SET x语句相当于这三个语句：

Setting collation_connection also sets
character_set_connection to the character set
associated with the collation.

When a client connects, it sends to the server 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.
当一个客户连接，它向Server发送它想使用的字符集的名字，Server用这名字把character_set_client, character_set_results, and character_set_connection 这些变量设置成那个字符集,
实际上，Server使用字符集名字执行了SET NAMES 操作

With the mysql client, it is not necessary to
execute SET NAMES every time you start up if
you want to use a character set different from the default. You
can add the --default-character-set option
setting to your mysql statement line, or in
your option file.
For example, the following option file setting
changes the three character set variables set to
koi8r each time you invoke
mysql:
如果你不想用默认字符集， mysql 客户端 不需要每次启动时执行SET NAMES 。
你可以加上 --default-character-set在 mysql执行语句行, 或在option file(configuration file)加上，
比如，下面的option file(my.ini)选项文件设置 使你每次执行mysql程序时把三句字符集变量
改成 koi8r：

Example: Suppose that column1 is defined as
CHAR(5) CHARACTER SET latin2. If you do not say
SET NAMES or SET CHARACTER
SET, then for SELECT column1 FROM t,
the server sends back all the values for
column1 using the character set that the client
specified when it connected. On the other hand, if you say
SET NAMES 'latin1' or SET CHARACTER
SET latin1 before issuing the SELECT
statement, the server converts the latin2
values to latin1 just before sending results
back. Conversion may be lossy if there are characters that are not
in both character sets.
例如：假设column1定义是 CHAR(5) CHARACTER SET latin2。如果你不用SET NAMES 或者 SET CHARACTER
SET，那么对于你的 SELECT column1 FROM t请求，Server
会把column1 的所有值用连接建立时 客户端指定的字符集 送回。另一方面，如果你
用了 SET NAMES 'latin1' or SET CHARACTER
SET latin1`，那么在送回结果之前，
Server会把 latin2 的值转成latin1，如果 有字符 不在两种字符集里，
转化Conversion可能会失败 。

If you do not want the server to perform any conversion of result
sets, set character_set_results to
NULL:
如果你不希望Server作任何转换，就把 character_set_results设置成 NULL

If
you are wondering why -despite all UTF8 settings- you still don't get
non-ASCII characters right, it might be the case that the _connection_
character set is still standard latin1.

To change the connection charset permanently to UTF-8, add the following line in the [mysqld] section:
要改变connection charset永久
成UTF-8 把下面这行加入[mysqld] :

[mysqld]
init-connect='SET NAMES utf8'

init代表 initial初始

The other way to let MySQL know what connection charset you intend to use
is per-connection based. After a connection is established (with host,
name, password), add the following two lines in your application:
把下面这二行加入你的应用程式
application:

SET NAMES utf8;
SET CHARACTER_SET utf8;

The
last hint is given most of the time, but not everybody is happy to
change every application (esp. when some lazy add-on and extension
programmers use their own connection stuff instead of the (PHP)
application.

When
a client connects, it sends to the server the name of the character set
that it wants to use. The server sets the character_set_client,
character_set_results, and character_set_connection variables to that
character set. (In effect, the server performs a SET NAMES operation
using the character set.)

With the mysql client, it is not
necessary to execute SET NAMES every time you start up if you want to
use a character set different from the default. You can add the
--default-character-set option setting to your mysql statement line, or
in your option file. For example, the following option file setting
changes the three character set variables set to koi8r each time you
run mysql:

[mysql]default-character-set=koi8r

Example:
Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you
do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM
t, the server sends back all the values for column1 using the character
set that the client specified when it connected. On the other hand, if
you say SET NAMES 'latin1' or SET CHARACTER SET latin1, then just
before sending results back, the server converts the latin2 values to
latin1. Conversion may be lossy if there are characters that are not in
both character sets.

If you do not want the server to perform any conversion, set character_set_results to NULL:

Copy right claim: 歡迎使用 本站的圖片,文字....
如果 你需要更高品質的圖片 Email 給我們 但有個謙卑的要求 連結到我們的網站
Welcome to use this web site pictures, words.......
If you need better quality picture, Email to us but with a humble request: link to our web site