PDO::__construct 的DSN 字符串格式詳解及寫法規範，幫你快速正確配置

gitbox 2025-09-19

需要把PDO 用起來，第一步就是把DSN（Data Source Name）寫對。本文系統梳理常見數據庫的DSN 語法、易錯點與最佳實踐，並給出可直接套用的示例代碼與校驗清單。

什麼是DSN？

DSN ：告訴PDO 要連接哪個驅動（mysql、pgsql、sqlite …）、哪台主機、哪個庫，以及其他連接參數。
位置： new PDO($dsn, $username, $password, $options)
結構： <driver>:<key>=<value>;[<key>=<value>;…]
區分大小寫：驅動名與鍵名通常不區分大小寫，但值區分（如文件路徑）。

常見驅動的DSN 寫法

1) MySQL（ mysql ）

基礎：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'mysql:host=127.0.0.1;port=3306;dbname=app;charset=utf8mb4'</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-string">'user'</span></span><span>, </span><span><span class="hljs-string">'pass'</span></span><span>, [
    PDO::</span><span><span class="hljs-variable constant_">ATTR_ERRMODE</span></span><span> =&gt; PDO::</span><span><span class="hljs-variable constant_">ERRMODE_EXCEPTION</span></span><span>,
]);
</span></span>

要點與可選項：

host ：可用域名/IP；若走UNIX Socket，用unix_socket=/path/mysql.sock ，不要再寫host/port。
dbname ：目標數據庫名。
charset ：強烈建議設為utf8mb4 。
port ：可省略，默認3306。
ssl ：新版本MySQL/驅動可通過PDO::MYSQL_ATTR_SSL_*選項配合（見文末“安全與性能選項”）。

UNIX Socket 示例：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'mysql:unix_socket=/var/run/mysqld/mysqld.sock;dbname=app;charset=utf8mb4'</span></span><span>;
</span></span>

多參數拼接安全寫法（防分號/特殊字符）：

 <span><span><span class="hljs-variable">$params</span></span><span> = [
    </span><span><span class="hljs-string">'host'</span></span><span>    =&gt; </span><span><span class="hljs-string">'db.internal'</span></span><span>,
    </span><span><span class="hljs-string">'port'</span></span><span>    =&gt; </span><span><span class="hljs-number">3306</span></span><span>,
    </span><span><span class="hljs-string">'dbname'</span></span><span>  =&gt; </span><span><span class="hljs-string">'app'</span></span><span>,
    </span><span><span class="hljs-string">'charset'</span></span><span> =&gt; </span><span><span class="hljs-string">'utf8mb4'</span></span><span>,
];
</span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'mysql:'</span></span><span> . </span><span><span class="hljs-title function_ invoke__">implode</span></span><span>(</span><span><span class="hljs-string">';'</span></span><span>, </span><span><span class="hljs-title function_ invoke__">array_map</span></span><span>(
    fn(</span><span><span class="hljs-variable">$k</span></span><span>,</span><span><span class="hljs-variable">$v</span></span><span>) =&gt; </span><span><span class="hljs-variable">$k</span></span><span> . </span><span><span class="hljs-string">'='</span></span><span> . </span><span><span class="hljs-title function_ invoke__">str_replace</span></span><span>(</span><span><span class="hljs-string">';'</span></span><span>, </span><span><span class="hljs-string">'\;'</span></span><span>, (</span><span><span class="hljs-keyword">string</span></span><span>)</span><span><span class="hljs-variable">$v</span></span><span>),
    </span><span><span class="hljs-title function_ invoke__">array_keys</span></span><span>(</span><span><span class="hljs-variable">$params</span></span><span>),
    </span><span><span class="hljs-variable">$params</span></span><span>
));
</span></span>

2) PostgreSQL（ pgsql ）

基礎：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'pgsql:host=127.0.0.1;port=5432;dbname=app'</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-string">'user'</span></span><span>, </span><span><span class="hljs-string">'pass'</span></span><span>);
</span></span>

要點：

host可為路徑（UNIX domain socket 目錄，通常是/var/run/postgresql ），此時可省略port 。
字符集建議在options或連接後執行SET NAMES ，或用options='--client_encoding=UTF8' 。
某些場景更偏好host=/var/run/postgresql寫法。

UNIX Socket 示例：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">"pgsql:host=/var/run/postgresql;dbname=app"</span></span><span>;
</span></span>

3) SQLite（ sqlite ）

文件數據庫：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'sqlite:/path/to/database.sqlite'</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>);
</span></span>

內存數據庫：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'sqlite::memory:'</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>);
</span></span>

要點：

路徑包含空格或分號時，建議真實文件路徑中避免特殊字符；Windows 下使用雙反斜杠或斜杠路徑： C:\\data\\db.sqlite或C:/data/db.sqlite 。

4) SQL Server（ sqlsrv驅動）

基礎：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'sqlsrv:Server=127.0.0.1,1433;Database=app'</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-string">'user'</span></span><span>, </span><span><span class="hljs-string">'pass'</span></span><span>);
</span></span>

要點：

Server可寫host,port或hostname\INSTANCE 。
建議通過選項設置編碼： PDO::SQLSRV_ATTR_ENCODING 。
TLS/加密通常通過驅動選項（見文末）。

命名實例：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'sqlsrv:Server=WIN-SRV\\SQLEXPRESS;Database=app'</span></span><span>;
</span></span>

5) Oracle（ oci / oci8 ）

基礎：

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'oci:dbname=//127.0.0.1:1521/ORCL;charset=AL32UTF8'</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-string">'user'</span></span><span>, </span><span><span class="hljs-string">'pass'</span></span><span>);
</span></span>

要點：

dbname支持//host:port/service_name或本地tnsnames.ora中的別名。
推薦AL32UTF8編碼。

6) IBM Db2（ ibm ）

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=APP;HOSTNAME=127.0.0.1;PORT=50000;PROTOCOL=TCPIP;'</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-string">'user'</span></span><span>, </span><span><span class="hljs-string">'pass'</span></span><span>);
</span></span>

7) Firebird（ firebird ）

 <span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">'firebird:dbname=localhost:/path/to/db.fdb;charset=UTF8'</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-string">'user'</span></span><span>, </span><span><span class="hljs-string">'pass'</span></span><span>);
</span></span>

實戰規範與易錯點

1) 鍵值用分號分隔，值裡出現分號怎麼辦？

規範： <driver>:key=value;key=value ，分號是分隔符。
若值可能包含分號（極少見），建議不要塞進DSN，而是改走PDO 第四參$options或換成不含分號的配置方式（如環境變量分發，再拼DSN）。

2) 編碼設置

MySQL ：在DSN 裡加charset=utf8mb4最直觀；連接後不要再SET NAMES以免重複。
PostgreSQL ：推薦在options裡設置--client_encoding=UTF8 ，或使用連接後SET client_encoding TO 'UTF8' 。
SQL Server ：通過選項PDO::SQLSRV_ATTR_ENCODING設置（如SQLSRV_ENCODING_UTF8 ）。

3) 主機、端口與套接字的互斥

MySQL 裡unix_socket與host/port不要混用。
PostgreSQL 的socket 通過將host指向目錄實現。

4) Windows 路徑與反斜杠

SQLite 等文件路徑在Windows 上推薦用雙反斜杠或正斜杠： C:\\path\\db.sqlite或C:/path/db.sqlite 。

5) 環境變量與配置中心

不把賬號密碼硬編碼進倉庫：
- .env ： DB_DSN=mysql:host=…;dbname=…;charset=utf8mb4
- 運行時： $pdo = new PDO(getenv('DB_DSN'), getenv('DB_USER'), getenv('DB_PASS'));

安全與性能選項（第四參$options ）

 <span><span><span class="hljs-variable">$opts</span></span><span> = [
    PDO::</span><span><span class="hljs-variable constant_">ATTR_ERRMODE</span></span><span>            =&gt; PDO::</span><span><span class="hljs-variable constant_">ERRMODE_EXCEPTION</span></span><span>, </span><span><span class="hljs-comment">// 拋異常，便於定位</span></span><span>
    PDO::</span><span><span class="hljs-variable constant_">ATTR_DEFAULT_FETCH_MODE</span></span><span> =&gt; PDO::</span><span><span class="hljs-variable constant_">FETCH_ASSOC</span></span><span>,       </span><span><span class="hljs-comment">// 默認返回關聯數組</span></span><span>
    PDO::</span><span><span class="hljs-variable constant_">ATTR_EMULATE_PREPARES</span></span><span>   =&gt; </span><span><span class="hljs-literal">false</span></span><span>,                  </span><span><span class="hljs-comment">// 讓驅動使用原生預處理（安全/性能更穩）</span></span><span>
];

</span><span><span class="hljs-comment">// MySQL SSL 示例（驅動支持時）：</span></span><span>
</span><span><span class="hljs-variable">$opts</span></span><span>[PDO::</span><span><span class="hljs-variable constant_">MYSQL_ATTR_SSL_CA</span></span><span>]   = </span><span><span class="hljs-string">'/etc/ssl/certs/ca.pem'</span></span><span>;
</span><span><span class="hljs-comment">// $opts[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false; // 若需跳過校驗（不推薦）</span></span><span>

</span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-variable">$user</span></span><span>, </span><span><span class="hljs-variable">$pass</span></span><span>, </span><span><span class="hljs-variable">$opts</span></span><span>);
</span></span>

提示：

預處理語句配合?或命名佔位符能避免SQL 注入。
生產環境務必開啟TLS/SSL（需要服務器與驅動共同支持）。

從配置數組安全構建DSN（可複用函數）

 <span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span><span> </span><span><span class="hljs-title">build_mysql_dsn</span></span><span>(</span><span><span class="hljs-params"><span class="hljs-keyword">array</span></span></span><span> </span><span><span class="hljs-variable">$cfg</span></span><span>): </span><span><span class="hljs-title">string</span></span><span> {
    </span><span><span class="hljs-comment">// 支持 host/port 或 unix_socket，自動過濾空值</span></span><span>
    </span><span><span class="hljs-variable">$parts</span></span><span> = [];
    </span><span><span class="hljs-variable">$parts</span></span><span>[] = </span><span><span class="hljs-keyword">isset</span></span><span>(</span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'unix_socket'</span></span><span>])
        ? </span><span><span class="hljs-string">'unix_socket='</span></span><span> . </span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'unix_socket'</span></span><span>]
        : </span><span><span class="hljs-string">'host='</span></span><span> . (</span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'host'</span></span><span>] ?? </span><span><span class="hljs-string">'127.0.0.1'</span></span><span>);

    </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-keyword">empty</span></span><span>(</span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'unix_socket'</span></span><span>]) &amp;&amp; !</span><span><span class="hljs-keyword">empty</span></span><span>(</span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'port'</span></span><span>])) {
        </span><span><span class="hljs-variable">$parts</span></span><span>[] = </span><span><span class="hljs-string">'port='</span></span><span> . (</span><span><span class="hljs-keyword">int</span></span><span>)</span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'port'</span></span><span>];
    }
    </span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-keyword">empty</span></span><span>(</span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'dbname'</span></span><span>])) {
        </span><span><span class="hljs-variable">$parts</span></span><span>[] = </span><span><span class="hljs-string">'dbname='</span></span><span> . </span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'dbname'</span></span><span>];
    }
    </span><span><span class="hljs-variable">$parts</span></span><span>[] = </span><span><span class="hljs-string">'charset='</span></span><span> . (</span><span><span class="hljs-variable">$cfg</span></span><span>[</span><span><span class="hljs-string">'charset'</span></span><span>] ?? </span><span><span class="hljs-string">'utf8mb4'</span></span><span>);

    </span><span><span class="hljs-keyword">return</span></span><span> </span><span><span class="hljs-string">'mysql:'</span></span><span> . </span><span><span class="hljs-title function_ invoke__">implode</span></span><span>(</span><span><span class="hljs-string">';'</span></span><span>, </span><span><span class="hljs-variable">$parts</span></span><span>);
}

</span><span><span class="hljs-comment">// 用法</span></span><span>
</span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-title function_ invoke__">build_mysql_dsn</span></span><span>([
    </span><span><span class="hljs-string">'host'</span></span><span>    =&gt; </span><span><span class="hljs-string">'db.internal'</span></span><span>,
    </span><span><span class="hljs-string">'port'</span></span><span>    =&gt; </span><span><span class="hljs-number">3306</span></span><span>,
    </span><span><span class="hljs-string">'dbname'</span></span><span>  =&gt; </span><span><span class="hljs-string">'app'</span></span><span>,
    </span><span><span class="hljs-string">'charset'</span></span><span> =&gt; </span><span><span class="hljs-string">'utf8mb4'</span></span><span>,
]);
</span><span><span class="hljs-comment">// new PDO($dsn, $user, $pass, $opts);</span></span><span>
</span></span>

連接失敗排查速查表

驅動未安裝： could not find driver → 檢查php -m中是否有pdo_mysql / pdo_pgsql等。
主機或端口錯誤： SQLSTATE[HY000] [2002] （MySQL）→ 校驗host/port或unix_socket 。
數據庫不存在： Unknown database 'xxx' → 修正dbname或先創建庫。
編碼問題：出現亂碼→ 確認DSN/選項中字符集一致（MySQL 用charset=utf8mb4 ，Pg 設client_encoding=UTF8 ）。
TLS/證書：SSL 握手失敗→ 檢查CA 路徑、服務端是否啟用TLS、證書匹配域名。
權限不足： permission denied或Access denied → 校驗用戶名/密碼、用戶在目標庫的權限。

常用DSN 速覽（可收藏）

 <span><span>MySQL
mysql:host=HOST;port=3306;dbname=DB;charset=utf8mb4
mysql:unix_socket=/path/mysql.sock;dbname=DB;charset=utf8mb4

PostgreSQL
pgsql:host=HOST;port=5432;dbname=DB
pgsql:host=/var/run/postgresql;dbname=DB

SQLite
sqlite:/absolute/path/to/db.sqlite
sqlite::memory:

SQL Server
sqlsrv:Server=HOST,1433;Database=DB
sqlsrv:Server=HOST\INSTANCE;Database=DB

Oracle
oci:dbname=//HOST:1521/SERVICE;charset=AL32UTF8

Db2
ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=DB;HOSTNAME=HOST;PORT=50000;PROTOCOL=TCPIP;

Firebird
firebird:dbname=HOST:/path/to/db.fdb;charset=UTF8
</span></span>

相關標籤:
PDO

PDO::__ construct