MySQL為什麼要set names

xuxubaby發表於2014-01-08
MySql
大家都知道
SET NAMES x
相當於
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

以下從MySQL5.0官方文件上摘錄了相關內容，並翻譯，說明了相關係統變數的用處：

What character set is the statement in when it leaves the client?
statement離開客戶端的時候是什麼字符集？

The server takes the character_set_client system variable to be the character set in which statements are sent by the client.
客戶端送過來的statement，伺服器認為它的字符集是系統變數character_set_client的值。

What character set should the server translate a statement to after receiving it?
伺服器收到一個statement後，會把它轉換成什麼字符集？

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). 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.
為了這一用途，伺服器使用系統變數character_set_connection和collation_connection。它把客戶端傳來的statement，從character_set_client字符集轉換成character_set_connection字符集（除非字串中有類似_latin1或者_utf8的字符集宣告）。collation_connection對於字串的比較是非常重要的。對於字元型別的欄位值的比較，collation_connection是不起作用的。因為欄位有自己的collation，有更高的優先順序。

What character set should the server translate to before shipping result sets or error messages back to the client?
在結果集由伺服器傳遞給客戶端之前，需要轉換成什麼字符集？

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系統變數表明瞭伺服器返回查詢結果時使用的字符集。返回的資料，有比如欄位的值和後設資料（例如欄位名）。

If you are using the mysql client with auto-reconnect enabled (which is not recommended), it is preferable to use the charset command rather than SET NAMES. For example:
如果你使用mysql客戶端的自動重連（不推薦使用），最好用charset命令，而不是SET NAMES。例如：

mysql> charset utf8
Charset changed

The charset command issues a SET NAMES statement, and also changes the default character set that is used if mysql reconnects after the connection has dropped. 
charset命令發出了一個SET NAMES語句，並且連線斷開後自動重連時使用的預設字符集也被修改了。

The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements. They have no other purpose.
如果CREATE TABLE中沒有明確指出字符集和collation，那麼database字符集和collation將做為預設值，它們沒有其他的用處。

The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server.
預設database的字符集和collation可以通過系統變數character_set_database和collation_database檢視。伺服器當預設database改變時設定這些變數的值。如果沒有預設的database，這些變數的將與對應的伺服器級的系統變數－character_set_server和collation_server的值相同。

The table character set and collation are used as default values if the column character set and collation are not specified in individual column definitions. The table character set and collation are MySQL extensions; there are no such things in standard SQL.
表的字符集和collation會作為預設值，如果列的定義中沒有指明字符集和collation的話。

Every “character” column (that is, a column of type CHAR, VARCHAR, or TEXT) has a column character set and a column collation.
每個“字元”欄位（即CHAR、VARCHAR或者TEXT型別的欄位）都有一個欄位字符集和欄位校驗（collation）。

Every character string literal has a character set and a collation.
每個字串有一個字符集和一個較驗。

A character string literal may have an optional character set introducer and COLLATE clause:
每個字串有一個可選的字符集introducer和COLLATE子句：

[_charset_name]'string' [COLLATE collation_name]

Examples:

SELECT 'string';
SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;

相關文章