問題描述

我們可以在初始化 PDO 時(shí)顯式設(shè)置字符集為 utf8，只需在 dsn 字符串中添加charset=utf8"即可.但是如何在使用 PDO 時(shí)顯式指定 MySQL 連接中使用的排序規(guī)則?

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?

我不想使用額外的查詢來執(zhí)行此操作:

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

SET NAMES utf8 COLLATE utf8_unicode_ci;

有沒有什么辦法不用SET NAMES"呢?或者，如果我不指定排序規(guī)則會(huì)有什么問題嗎?

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(連接選項(xiàng))中設(shè)置它.

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

DSN 更好，我認(rèn)為.

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 的默認(rèn)排序規(guī)則，除非您的數(shù)據(jù)庫表或字段使用不同的內(nèi)容.

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.

如果您希望整個(gè)服務(wù)器使用此默認(rèn)排序規(guī)則進(jìn)行響應(yīng)，請(qǐng)使用配置指令:

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

因此您不必每次都在連接時(shí)指定它.

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

排序規(guī)則會(huì)影響字符的排序，并在數(shù)據(jù)庫中的表和字段上設(shè)置.查詢表時(shí)，會(huì)遵守這些設(shè)置.確保它們已設(shè)置.使用 UTF-8 名稱和數(shù)據(jù)庫中設(shè)置的排序規(guī)則.

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.

您的評(píng)論:

人們應(yīng)該知道字符集和排序規(guī)則是兩件不同的事情."

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

讓我們引用 MySQL 手冊(cè)來證明這個(gè):

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

一個(gè) SET NAMES 'charset_name' 語句相當(dāng)于這三個(gè)聲明:

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 設(shè)置為 charset_name 也會(huì)隱式地將 collat??ion_connection 設(shè)置為默認(rèn)排序規(guī)則charset_name.

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

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

來自評(píng)論的問題:

如何確保我不會(huì)把事情搞砸，因?yàn)槲业淖雷硬皇悄J(rèn)排序規(guī)則utf8_general_ci?

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

示例:列排序規(guī)則覆蓋表排序規(guī)則

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

如果在列上同時(shí)指定了 CHARACTER SET X 和 COLLATE Y，則使用字符集 X 和排序規(guī)則 Y.該列具有表列中指定的字符集 utf8 和排序規(guī)則 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.

示例:通常使用表格整理

如果未在列/字段上明確指定排序規(guī)則，則使用表排序規(guī)則:

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 具有排序規(guī)則 latin1_bin.

col1 has collation latin1_bin.

如果您想要 utf8_unicode_ci 整理，請(qǐng)將其設(shè)置為您的一般表格或列/字段.

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

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