PHP / MySQL 中的特殊字符

Special characters in PHP / MySQL

提问by Jonathan

I have in the database words that include special character (in Spanish mostly, like tildes). In the database everything is saved and shown correctly with PHPmyAdmin, but when I get the data (using PHP) and display it in a browser, I get a weird character, like a "?" with a square... I need a general fix so I don't need to escape each character every time, and also I would be able to insert special Spanish characters from a PHP form into the database...

我在数据库中有包含特殊字符的单词（主要是西班牙语，如波浪号）。在数据库中，所有内容都使用 PHPmyAdmin 保存并正确显示，但是当我获取数据（使用 PHP）并将其显示在浏览器中时，我得到一个奇怪的字符，例如“？” 用一个正方形...我需要一个通用的修复，所以我不需要每次都转义每个字符，而且我还可以将特殊的西班牙语字符从 PHP 表单插入到数据库中...

The HTML is correct:

HTML 是正确的：

<meta http-equiv="content-type" content="text/html; charset=utf-8" />

All tables and databas are set to utf8_spanish

所有表和数据库都设置为 utf8_spanish

The character I get: ?

我得到的字符： ?

Any suggestions???

有什么建议？？？

Thanks!!!

谢谢！！！

采纳答案by Jonathan

Changed the HTML charset to ISO-8859-1 fixed the problem! Silly

将 HTML 字符集更改为 ISO-8859-1 解决了问题！愚蠢的

回答by Stefan Gehrig

I'd just like to provide some more details on the solution proposed by vartecwhich is (depending on your MySQL installation) the most correct solution to your problem. First of all the character set / encoding issue in MySQL is a somewhat complex subject which is extensively covered in the MySQL manual Chapter 9.1 "Character Set Support". In your case especially 9.1.4. "Connection Character Sets and Collations"will be most relevant.

我只想提供有关vartec提出的解决方案的更多详细信息，这是（取决于您的 MySQL 安装）最正确的问题解决方案。首先，MySQL 中的字符集/编码问题是一个有点复杂的主题，在 MySQL 手册第 9.1 章“字符集支持”中有广泛的介绍。在您的情况下，尤其是9.1.4。“连接字符集和排序规则”将是最相关的。

To make it short: MySQL must know which character set / encoding your client application (talking from the database persoective that's your PHP script) is expecting as it'll transcode all the string data from the internal character set / encoding defined at server-, database-, table- or column-level into the connection character set / encoding. You're using UTF-8 on the client side so must tell MySQL that you use UTF-8. This is done by the MySQL command SET NAMES 'utf8'which must be sent as the first query on opening a connection. Depending on your installation and on the MySQL client library you use in the PHP script this can be done automatically on each connect.

简而言之：MySQL 必须知道您的客户端应用程序（从数据库透视这是您的 PHP 脚本）所期望的字符集/编码，因为它将从服务器定义的内部字符集/编码中转码所有字符串数据，数据库级、表级或列级进入连接字符集/编码。您在客户端使用 UTF-8，因此必须告诉 MySQL 您使用 UTF-8。这是由 MySQL 命令完成的，该命令SET NAMES 'utf8'必须作为打开连接的第一个查询发送。根据您的安装和您在 PHP 脚本中使用的 MySQL 客户端库，这可以在每次连接时自动完成。

If you use PDO it's just a matter of setting a configuration parameter

如果您使用 PDO，则只需设置配置参数

$db = new PDO($dsn, $user, $password);
$db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");

Using mysqli changing the client character set / encoding is even more simple:

使用 mysqli 更改客户端字符集/编码更加简单：

$mysqli = new mysqli("localhost", "user", "password", "db");
$mysqli->set_charset("utf8");

I hope that will help to make the whole thing more understandable.

我希望这将有助于使整个事情更容易理解。

回答by Quassnoi

Issue SET NAMES 'utf8'right after connecting:

发出SET NAMES 'utf8'连接之后：

$mysqli = new mysqli("localhost", "user", "password", "database");
$mysqli->query("SET NAMES 'utf8'");

回答by vartec

Have MySQL translate it automatically

让 MySQL 自动翻译它

$conn = mysql_connect('host', 'user', 'password');
mysql_set_charset('utf8',$conn);

http://es.php.net/manual/en/function.mysql-set-charset.php

http://es.php.net/manual/en/function.mysql-set-charset.php

EDIT: from your comment I gather, that this is actually encoded in latin1 so

编辑：根据我收集的您的评论，这实际上是用 latin1 编码的，所以

mysql_set_charset('latin1_spanish_ci',$conn);

回答by user117001

I found this from somewhere and since then have been using it as whole without thinking too much.

我从某个地方发现了这个，从那时起就一直在使用它，没有想太多。

mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");

回答by Omar

holy crap.. this was driving me insane. i was trying to do the same thing. i added a utf-8 encoding HTML header.. mysqli_set_charset() saved me, hours later, but I am glad I have it working now

天哪……这让我发疯了。我试图做同样的事情。我添加了一个 utf-8 编码的 HTML 标头.. mysqli_set_charset() 救了我，几个小时后，但我很高兴我现在可以使用它

another function to try for encoding special characters and accents is htmlentities()

另一个尝试编码特殊字符和重音的函数是 htmlentities()

回答by Marco Rosas

You can simply do:

你可以简单地做：

$link = mysql_connect("host", "user", "password");
mysql_select_db("database", $link); mysql_query("SET NAMES 'utf8';", $link);

回答by justanotherITguy

The answer that worked for me is the one that Stefan Gehrig posted:

对我有用的答案是 Stefan Gehrig 发布的答案：

$mysqli->set_charset("utf8");

$mysqli->set_charset("utf8");

I just needed to add that line after having defined the connection and my problems where solved! :)

我只需要在定义连接和解决我的问题后添加该行！:)

(I am posting an answer since I cannot comment...).

（我正在发布答案，因为我无法发表评论......）。

回答by soulmerge

Are you sure you have UTF8 data in your database to begin with?

您确定您的数据库中有 UTF8 数据吗？

回答by Ionu? G. Stan

When dealing with special characters I always take care of the following:

在处理特殊字符时，我总是注意以下几点：

• Database, table and field character sets are all set to utf8_general_* or utf8_unicode_*
• I make sure my editor saves PHP files with the right character set
• I set default_charset in php.ini to UTF-8 or
• I send a Content-Type: text/html; charset=UTF-8 header
• The charset in the META tag is UTF-8 (this is overriden by the Content-Type HTTP header)
• When connecting to MySQL I issue the following queries:
  • SET NAMES utf8
  • SET CHARACTER SET utf8
  • SET COLLATION_CONNECTION="utf8_general_ci"/"utf8_general_ci"

• 数据库、表和字段字符集都设置为 utf8_general_* 或 utf8_unicode_*
• 我确保我的编辑器使用正确的字符集保存 PHP 文件
• 我将 php.ini 中的 default_charset 设置为 UTF-8或
• 我发送一个 Content-Type: text/html; 字符集=UTF-8 标头
• META 标记中的字符集是 UTF-8（这被 Content-Type HTTP 标头覆盖）
• 连接到 MySQL 时，我发出以下查询：
  • 设置名称 utf8
  • 设置字符集 utf8
  • SET COLLATION_CONNECTION="utf8_general_ci"/"utf8_general_ci"