問題描述

我們可以在初始化 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.

這篇關于如何在沒有 SET NAMES 的情況下使用 PDO 指定排序規則?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！