mysqli::set_charset 設置字符集後，怎樣才能確保數據寫入數據庫時字符不會出錯？

gitbox 2025-09-04

<span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-comment">// 前置無關代碼示例</span></span><span>
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"初始化程序環境...&lt;br&gt;"</span></span><span>;
</span><span><span class="hljs-variable">$version</span></span><span> = </span><span><span class="hljs-title function_ invoke__">phpversion</span></span><span>();
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"PHP版本："</span></span><span> . </span><span><span class="hljs-variable">$version</span></span><span> . </span><span><span class="hljs-string">"&lt;br&gt;"</span></span><span>;
</span><span><span class="hljs-meta">?&gt;</span></span><span>
</span></span>

在使用PHP 的mysqli擴展進行數據庫操作時，字符集問題是最容易導致數據寫入亂碼或出錯的因素之一。 mysqli::set_charset是官方推薦的設置數據庫連接字符集的方法，但設置後仍需要注意一些細節，才能確保數據正確寫入數據庫。

1. 設置字符集的基本方法

<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_errno) {
    </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">// 設置字符集為 utf8mb4</span></span><span>
</span><span><span class="hljs-keyword">if</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-string">"utf8mb4"</span></span><span>)) {
    </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;error);
}

</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"字符集設置成功: "</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>

這裡utf8mb4是推薦使用的字符集，因為它支持完整的Unicode，包括emoji 等特殊字符。

2. 確保數據庫表和字段字符集一致

即使連接字符集設置正確，如果數據庫表或字段的字符集不同，仍然會出現亂碼。可以通過SQL 查詢檢查表和字段的字符集：

 <span><span><span class="hljs-keyword">SHOW</span></span><span> </span><span><span class="hljs-keyword">CREATE</span></span><span> </span><span><span class="hljs-keyword">TABLE</span></span><span> your_table;
</span></span>

建議將表和字段字符集統一設置為utf8mb4 ：

 <span><span><span class="hljs-keyword">ALTER</span></span><span> </span><span><span class="hljs-keyword">TABLE</span></span><span> your_table </span><span><span class="hljs-keyword">CONVERT</span></span><span> </span><span><span class="hljs-keyword">TO</span></span><span> </span><span><span class="hljs-type">CHARACTER</span></span><span> </span><span><span class="hljs-keyword">SET</span></span><span> utf8mb4 </span><span><span class="hljs-keyword">COLLATE</span></span><span> utf8mb4_unicode_ci;
</span></span>

3. 使用預處理語句（Prepared Statements）

直接拼接SQL 字符串容易出現字符編碼問題或SQL 注入風險。使用預處理語句可以保證數據以正確編碼寫入：

 <span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"INSERT INTO your_table (name, content) VALUES (?, ?)"</span></span><span>);
</span><span><span class="hljs-variable">$name</span></span><span> = </span><span><span class="hljs-string">"測試用戶"</span></span><span>;
</span><span><span class="hljs-variable">$content</span></span><span> = </span><span><span class="hljs-string">"這是一些包含特殊字符的內容 ??"</span></span><span>;
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"ss"</span></span><span>, </span><span><span class="hljs-variable">$name</span></span><span>, </span><span><span class="hljs-variable">$content</span></span><span>);
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span></span>

mysqli會自動根據連接字符集處理參數，從而減少字符亂碼風險。

4. 確保網頁和PHP 文件編碼一致

PHP 文件本身應保存為UTF-8 無BOM。
HTML 頁面應聲明字符集：

 <span><span><span class="hljs-tag">&lt;<span class="hljs-name">meta</span></span></span><span> </span><span><span class="hljs-attr">charset</span></span><span>=</span><span><span class="hljs-string">"UTF-8"</span></span><span>&gt;
</span></span>

這樣，用戶輸入的數據、PHP 處理的字符串以及寫入數據庫的字符集保持一致。

5. 總結

使用$mysqli->set_charset("utf8mb4")設置連接字符集。
確保數據庫、表、字段字符集與連接字符集一致。
盡量使用預處理語句插入數據。
保證PHP 文件和頁面編碼為UTF-8。

按照這些步驟操作，可以最大程度避免數據寫入數據庫時出現字符錯誤或亂碼。

相關標籤:
mysqli