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