如何在没有 SET NAMES 的情况下使用 PDO 指定排序规则?

2021-12-26 00:00:00 php mysql pdo

我们可以在初始化 PDO 时显式设置字符集为 utf8，只需在 dsn 字符串中添加charset=utf8"即可.但是如何在使用 PDO 时显式指定 MySQL 连接中使用的排序规则?

We can explicitly set the char set to utf8 when initializing PDO, just add "charset=utf8" to the dsn string. But how does one explicitly specify the collation used in MySQL connection when using PDO?

我不想使用额外的查询来执行此操作:

I don't want to use an additional query to do this:

SET NAMES utf8 COLLATE utf8_unicode_ci;

有没有什么办法不用SET NAMES"呢?或者，如果我不指定排序规则会有什么问题吗?

Is there any way without having to resort to "SET NAMES"? Or, would there be any problem if I don't specify a collation?

推荐答案

这里是二合一的答案.

您可以在 DSN 或 MYSQL_ATTR_INIT_COMMAND(连接选项)中设置它.

You can set this in the DSN or as MYSQL_ATTR_INIT_COMMAND (connection options).

DSN 更好，我认为.

DSN is better, i think.

$connect = new PDO(
  "mysql:host=$host;dbname=$db;charset=utf8", 
  $user, 
  $pass, 
  array(
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
  )
);

如果您指定 UTF-8，您将使用 utf8_general_ci 的默认排序规则，除非您的数据库表或字段使用不同的内容.

If you specify UTF-8 you are working with the default collation of utf8_general_ci, unless your db table or field uses something different.

如果您希望整个服务器使用此默认排序规则进行响应，请使用配置指令:

If you want the whole server to respond with this default collation then use configuration directives:

collation_server=utf8_unicode_ci 
character_set_server=utf8

因此您不必每次都在连接时指定它.

So you don't have to specify it on connection everytime.

排序规则会影响字符的排序，并在数据库中的表和字段上设置.查询表时，会遵守这些设置.确保它们已设置.使用 UTF-8 名称和数据库中设置的排序规则.

The collations affect the sorting of chars and is set on the table and fields in your database. These settings are respected, when querying the table. Make sure they are set. Use UTF-8 names with the collation set in your db.

您的评论:

人们应该知道字符集和排序规则是两件不同的事情."

"People should know char set and collation are 2 different things."

让我们引用 MySQL 手册来证明这个:

Let's Quote from the MySQL Manual to proof this:

一个 SET NAMES 'charset_name' 语句相当于这三个声明:

A SET NAMES 'charset_name' statement is equivalent to these three statements:

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

将 character_set_connection 设置为 charset_name 也会隐式地将 collat​​ion_connection 设置为默认排序规则charset_name.

Setting character_set_connection to charset_name also implicitly sets collation_connection to the default collation for charset_name.

我的回答:它是隐式工作的，除非您的表显式更改它.

来自评论的问题:

如何确保我不会把事情搞砸，因为我的桌子不是默认排序规则utf8_general_ci?

How to make sure I don't mess things up as my tables are not the default collation utf8_general_ci?

示例:列排序规则覆盖表排序规则

CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
) CHARACTER SET latin1 COLLATE latin1_bin;

如果在列上同时指定了 CHARACTER SET X 和 COLLATE Y，则使用字符集 X 和排序规则 Y.该列具有表列中指定的字符集 utf8 和排序规则 utf8_unicode_ci，而该表在 latin1 + latin1_bin 中.

If both CHARACTER SET X and COLLATE Y are specified on a column, character set X and collation Y are used. The column has character set utf8 and collation utf8_unicode_ci as specified in the table column, while the table is in latin1 + latin1_bin.

示例:通常使用表格整理

如果未在列/字段上明确指定排序规则，则使用表排序规则:

If collation is not explicitly specified on a column/Field, then the table collation is used:

CREATE TABLE t1
(
    col1 CHAR(10)
) CHARACTER SET latin1 COLLATE latin1_bin;

col1 具有排序规则 latin1_bin.

col1 has collation latin1_bin.

如果您想要 utf8_unicode_ci 整理，请将其设置为您的一般表格或列/字段.

If you want utf8_unicode_ci collation, set it to your tables in general or to the columns/fields.

相关文章