php 是否需要“SET CHARACTER SET utf8”？

Is "SET CHARACTER SET utf8" necessary?

提问by Znarkus

I′m rewritting our database class (PDO based), and got stuck at this. I′ve been taught to both use SET NAMES utf8and SET CHARACTER SET utf8when working with UTF-8 in PHP and MySQL.

我正在重写我们的数据库类（基于 PDO），并被困在这个问题上。我已经学会了在 PHP 和 MySQL 中使用SET NAMES utf8和SET CHARACTER SET utf8使用 UTF-8。

In PDO I now want to use the PDO::MYSQL_ATTR_INIT_COMMANDparameter, but it only supports one query.

在 PDO 中，我现在想使用该PDO::MYSQL_ATTR_INIT_COMMAND参数，但它仅支持一个查询。

Is SET CHARACTER SET utf8necessary?

有SET CHARACTER SET utf8必要吗？

回答by Stefan Gehrig

Using SET CHARACTER SET utf8after using SET NAMES utf8will actually reset the character_set_connectionand collation_connectionto
@@character_set_databaseand @@collation_databaserespectively.

利用SET CHARACTER SET utf8使用后SET NAMES utf8实际上会重置character_set_connection，并collation_connection以
@@character_set_database和@@collation_database分别。

The manualstates that

该手册指出

• SET NAMES xis equivalent to

  SET character_set_client = x;
  SET character_set_results = x;
  SET character_set_connection = x;
  

• and SET CHARACTER SET xis equivalent to

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

• SET NAMES x相当于

  SET character_set_client = x;
  SET character_set_results = x;
  SET character_set_connection = x;
  

• 并且SET CHARACTER SET x等价于

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

whereas SET collation_connection = xalso internally executes SET character_set_connection = <<character_set_of_collation_x>>and SET character_set_connection = xinternally also executes SET collation_connection = <<default_collation_of_character_set_x.

而SET collation_connection = x也在内部执行SET character_set_connection = <<character_set_of_collation_x>>和SET character_set_connection = x内部也执行SET collation_connection = <<default_collation_of_character_set_x。

So essentially you're resetting character_set_connectionto @@character_set_databaseand collation_connectionto @@collation_database. The manual explains the usage of these variables:

所以基本上你重新character_set_connection给@@character_set_database和collation_connection给@@collation_database。手册解释了这些变量的用法：

What character set should the server translate a statement to after receiving it?

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 和 collat​​ion_connection 系统变量。它将客户端发送的语句从 character_set_client 转换为 character_set_connection（具有介绍人的字符串文字除外，例如 _latin1 或 _utf8）。collat​​ion_connection 对于文字字符串的比较很重要。对于字符串与列值的比较， collat​​ion_connection 无关紧要，因为列有自己的排序规则，具有更高的排序规则优先级。

To sum this up, the encoding/transcoding procedure MySQL uses to process the query and its results is a multi-step-thing:

总结一下，MySQL 用来处理查询及其结果的编码/转码过程是一个多步骤的事情：

1. MySQL treats the incoming query as being encoded in character_set_client.
2. MySQL transcodes the statement from character_set_clientinto character_set_connection
3. when comparing string values to column values MySQL transcodes the string value from character_set_connectioninto the character set of the given database column and uses the column collation to do sorting and comparison.
4. MySQL builds up the result set encoded in character_set_results(this includes result data as well as result metadata such as column names and so on)

1. MySQL 将传入的查询视为以character_set_client.
2. MySQL 将语句从转码character_set_client为character_set_connection
3. 当将字符串值与列值进行比较时，MySQL 将字符串值转码为character_set_connection给定数据库列的字符集，并使用列排序规则进行排序和比较。
4. MySQL 建立编码的结果集character_set_results（这包括结果数据以及结果元数据，如列名等）

So it could be the case that a SET CHARACTER SET utf8would not be sufficient to provide full UTF-8 support. Think of a default database character set of latin1and columns defined with utf8-charset and go through the steps described above. As latin1cannot cover all the characters that UTF-8 can cover you may lose character information in step 3.

因此，aSET CHARACTER SET utf8可能不足以提供完整的 UTF-8 支持。考虑latin1使用utf8-charset定义的默认数据库字符集和列，并执行上述步骤。由于latin1无法覆盖 UTF-8 可以覆盖的所有字符，因此您可能会在步骤3 中丢失字符信息。

• Step 3:Given that your query is encoded in UTF-8 and contains characters that cannot be represented with latin1, these characters will be lost on transcoding from utf8to latin1(the default database character set) making your query fail.

• 第3步：鉴于您的查询以 UTF-8 编码并且包含无法用 表示的latin1字符，这些字符将在从utf8to latin1（默认数据库字符集）转码时丢失，从而使您的查询失败。

So I think it's safe to say that SET NAMES ...is the correct way to handle character set issues. Even though I might add that setting up your MySQL server variables correctly (all the required variables can be set statically in your my.cnf) frees you from the performance overhead of the extra query required on every connect.

所以我认为可以肯定地说这SET NAMES ...是处理字符集问题的正确方法。尽管我可能会补充说正确设置 MySQL 服务器变量（所有必需的变量都可以在您的 中静态设置my.cnf）可以使您免于每次连接所需的额外查询的性能开销。

回答by soulmerge

From the mysql manual:

从mysql 手册：

SET CHARACTER SET is similar to SET NAMES but sets character_set_connectionand collation_connectionto character_set_databaseand collation_database. A SET CHARACTER SET xstatement is equivalent to these three statements:

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

SET CHARACTER SET 类似于 SET NAMES，但设置 character_set_connectionand collation_connection到 character_set_databaseand collation_database。一个SET CHARACTER SET x语句相当于这三个语句：

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

回答by Daren Schwenke

Since needing to support international characters sets, I've always just set the the character set of the text type fields on database creation.

由于需要支持国际字符集，我总是在创建数据库时设置文本类型字段的字符集。

I've also always used UTF-8.

我也一直使用 UTF-8。

Within PHP set the same:

在 PHP 中设置相同：

mb_internal_encoding( 'UTF-8' );