如何指定不带SET NAMES的PDO的排序规则? [英] How to specify collation with PDO without SET NAMES?

问题描述

初始化PDO时，我们可以将char set显式设置为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?

推荐答案

这是一个二合一的答案.

Here is a two in one answer.

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

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

我认为DSN更好.

$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也会将collation_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.当表位于latin1 + latin1_bin中时，该列具有在表列中指定的字符集utf8和排序规则utf8_unicode_ci.

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.

这篇关于如何指定不带SET NAMES的PDO的排序规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！