如何使用mysqli::character_set_name 確認數據庫字符集是否與應用字符集匹配？

gitbox 2025-07-09

<span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-comment">// 本文示例為 PHP 使用 mysqli::character_set_name 檢查數據庫連接字符集</span></span><span>
</span><span><span class="hljs-comment">// 請確保你已正確配置數據庫連接參數</span></span><span>

</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-string">"localhost"</span></span><span>, </span><span><span class="hljs-string">"username"</span></span><span>, </span><span><span class="hljs-string">"password"</span></span><span>, </span><span><span class="hljs-string">"database"</span></span><span>);

</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;connect_error) {
    </span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">"連接失敗: "</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;connect_error);
}

</span><span><span class="hljs-comment">// 設置期望的字符集</span></span><span>
</span><span><span class="hljs-variable">$expectedCharset</span></span><span> = </span><span><span class="hljs-string">'utf8mb4'</span></span><span>;
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">set_charset</span></span><span>(</span><span><span class="hljs-variable">$expectedCharset</span></span><span>);

</span><span><span class="hljs-comment">// 獲取當前連接使用的字符集</span></span><span>
</span><span><span class="hljs-variable">$currentCharset</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">character_set_name</span></span><span>();

</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"當前字符集: <span class="hljs-subst">$currentCharset</span></span></span><span>\n";

</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$currentCharset</span></span><span> === </span><span><span class="hljs-variable">$expectedCharset</span></span><span>) {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"字符集匹配，應用可以安全操作數據。\n"</span></span><span>;
} </span><span><span class="hljs-keyword">else</span></span><span> {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"字符集不匹配，當前為 <span class="hljs-subst">$currentCharset</span></span></span><span>，應為 </span><span><span class="hljs-subst">$expectedCharset</span></span><span>，請檢查設置。\n";
}

</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span><span><span class="hljs-meta">?&gt;</span></span><span>

------------------------------------------------------------

</span><span><span class="hljs-comment"># 如何使用 mysqli::character_set_name 確認數據庫字符集是否與應用字符集匹配？</span></span><span>

在開發 PHP 應用時，字符集問題是影響數據正確讀寫與顯示的常見問題之一。特別是在涉及多語言內容時，字符集不一致可能會導致亂碼、數據丟失甚至安全隱患。因此，在與 MySQL 數據庫建立連接後，確認當前使用的字符集是否與應用期望的一致，是非常重要的一步。

</span><span><span class="hljs-comment">## 什麼是 `mysqli::character_set_name`？</span></span><span>

`mysqli::</span><span><span class="hljs-title function_ invoke__">character_set_name</span></span><span>()` 是 PHP `mysqli` 類中的一個方法，它返回當前數據庫連接所使用的字符集名稱。通過這個方法，我們可以方便地確認當前連接實際使用的字符集，避免因誤配置而引發問題。

</span><span><span class="hljs-comment">## 使用方法</span></span><span>

你可以在成功建立數據庫連接後調用此方法。以下是一個基礎的使用示例：

```php
</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-string">"localhost"</span></span><span>, </span><span><span class="hljs-string">"user"</span></span><span>, </span><span><span class="hljs-string">"password"</span></span><span>, </span><span><span class="hljs-string">"database"</span></span><span>);

</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;connect_error) {
    </span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">"連接失敗: "</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;connect_error);
}

</span><span><span class="hljs-variable">$charset</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">character_set_name</span></span><span>();
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"當前使用的字符集是: <span class="hljs-subst">$charset</span></span></span><span>";
</span></span>

默認情況下， mysqli會使用latin1字符集，這往往並不符合現代應用（尤其是中文應用）的需求。因此，我們通常需要手動設置字符集，例如utf8mb4 。

 <span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">set_charset</span></span><span>(</span><span><span class="hljs-string">"utf8mb4"</span></span><span>);
</span></span>

設置後，再調用character_set_name()來確認設置是否生效。

檢查字符集是否匹配

在實際應用中，建議你寫一段邏輯代碼自動進行字符集匹配檢測：

 <span><span><span class="hljs-variable">$expectedCharset</span></span><span> = </span><span><span class="hljs-string">'utf8mb4'</span></span><span>;
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">set_charset</span></span><span>(</span><span><span class="hljs-variable">$expectedCharset</span></span><span>);

</span><span><span class="hljs-variable">$currentCharset</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">character_set_name</span></span><span>();

</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$currentCharset</span></span><span> !== </span><span><span class="hljs-variable">$expectedCharset</span></span><span>) {
    </span><span><span class="hljs-comment">// 記錄錯誤或發出警告</span></span><span>
    </span><span><span class="hljs-title function_ invoke__">error_log</span></span><span>(</span><span><span class="hljs-string">"字符集不匹配: 當前為 <span class="hljs-subst">$currentCharset</span></span></span><span>，應為 </span><span><span class="hljs-subst">$expectedCharset</span></span><span>");
}
</span></span>

為什麼選擇utf8mb4 ？

雖然MySQL 提供了utf8 ，但實際上它並不支持完整的UTF-8 字符集（最多只支持三字節編碼），而utf8mb4則是對完整UTF-8 的支持，包括表情符等四字節字符。因此，在現代Web 應用中推薦統一使用utf8mb4 。

總結

使用mysqli::character_set_name()是一個簡單而有效的方式來確認數據庫連接的實際字符集。在設置字符集之後應立即驗證，避免因編碼不一致導致的問題。與之配合的還有數據庫表結構定義、HTTP 編碼頭等，都需要一致性地設置為同一字符集，才能確保數據的正確存儲與展示。

 <span></span>

相關標籤:
mysqli