使用 PHP 和 MySQL，如何正确地将智能引号写入数据库？

发布于 2024-12-28 06:45:59 字数 527 浏览 3 评论 0原文

我有一个 PHP 网站，上面有 CLEditor 富文本控件。当我尝试将欧元和英镑写入数据库时，该字符运行得很好，因为我在包含页面 HTML、富文本控件 IFRAME HTML 和 MySQL 表排序规则中将字符集设置为 UTF-8。这方面一切都很好。但是，当我尝试编写智能引号时，我最终在数据库中看到了以下输出

This is a â€œtestâ€.

：(如果在您的浏览器中没有正确显示，则测试单词前面有一个拉丁语 a、一个欧元符号和一个小 AE 符号，后面有一个拉丁语 a 和一个欧元符号。）

当我使用PHP阅读时该值从数据库中返回以将其显示在页面上，它最终会变成带有问号以及其他一些拉丁字符的黑色菱形。

我应该做什么来解决这个问题？

原文

I have a PHP website with the CLEditor richtext control on it. When I try to write Euros and British Pounds to the database, the character goes through just fine because I have the charset set to UTF-8 in the containing page HTML, in the richtext control IFRAME HTML, and in the MySQL table collation. All is well on that front. However, when I try to write smart quotes, I end up seeing this output in the database:

This is a â€œtestâ€.

(If that doesn't show up properly above in you browser, the test word has something like a Latin a, a Euro symbol, and the small AE symbol in front of the word, and a Latin a and a Euro symbol after it.)

When I use PHP to read that value back out of the database to display it on the page, it ends up as black diamonds with question marks on them as well as some other Latin characters.

What should I be doing to fix this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夜深人未静 2025-01-04 06:45:59

首先，确保您的 MySQL 表使用 UTF-8 作为编码。如果是，它将如下所示：

mysql> SHOW CREATE TABLE Users (
...
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 |

接下来，确保您的 HTML 页面设置为显示 UTF-8：

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

然后它应该可以工作。

编辑：我故意不谈论整理，因为我认为它已经被考虑过，但为了每个人的利益，让我在这个答案中添加更多内容。

你说，

我在 MySQL 表排序规则中将字符集设置为 UTF-8 ...。

表排序规则与字符集不同。

排序是一种自动尝试将一种字符集转换为另一种字符集的行为，出于查询的目的。例如，如果您有 latin1 字符集和 UTF-8 排序规则，并且您执行类似 SELECT * FROM foo WHERE bar LIKE '%—%'; 的操作(UTF-8 U+2014) 位于字符集为 latin1 且与 L+0151 或 U+2014 匹配的表上。

并非巧合...如果您将这个 latin1 编码的字符输出到 UTF-8 编码的网页上，您将得到以下结果：

这是一个“测试”。

这似乎正是你的问题的输出。下面是复制它的 HTML：

<?php
$string = "This is a “test”.";
?>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html;charset=utf8"/>
    </head>
    <body>
        <p><?php echo $string; ?></p>
    </body>
</html>

请确保将此文件保存为 latin1...

要查看表设置为的字符集，请运行此查询：

SELECT CCSA.character_set_name, TABLE_COLLATION FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "database"
  AND T.table_name = "table";

仅适合您使用的正确结果（除非您'使用多种非英语语言）是：

+--------------------+-----------------+
| character_set_name | TABLE_COLLATION |
+--------------------+-----------------+
| utf8               | utf8_general_ci |
+--------------------+-----------------+

感谢您的支持；-)

First, make sure your MySQL table is using UTF-8 as its encoding. If it is, it will look like this:

mysql> SHOW CREATE TABLE Users (
...
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 |

Next, make sure your HTML page is set to display UTF-8:

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

Then it should work.

EDIT: I purposefully did not talk about collation, because I thought it was already considered, but for the benefit of everyone, let me add some more to this answer.

You state,

I have the charset set to UTF-8 … in the MySQL table collation.

Table collation is not the same thing as charset.

Collation is the act of automagically trying to convert one charset to another FOR THE PURPOSES OF QUERYING. E.g., if you have a charset of latin1 and a collation of UTF-8, and you do something like SELECT * FROM foo WHERE bar LIKE '%—%'; (UTF-8 U+2014) on a table with a charset of latin1 that match either L+0151 or U+2014.

Not so coincidentally... if you were output this latin1 encoded character onto a UTF-8 encoded web page, you will get the following:

This is a â€œtestâ€.

That seems to be the output of your problem, exactly. Here's the HTML to duplicate it:

<?php
$string = "This is a “test”.";
?>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html;charset=utf8"/>
    </head>
    <body>
        <p><?php echo $string; ?></p>
    </body>
</html>

Make sure you save this file in latin1...

To see what charset your table is set to, run this query:

SELECT CCSA.character_set_name, TABLE_COLLATION FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "database"
  AND T.table_name = "table";

The only proper results for your uses (unless you're using multiple non-English languages) is:

+--------------------+-----------------+
| character_set_name | TABLE_COLLATION |
+--------------------+-----------------+
| utf8               | utf8_general_ci |
+--------------------+-----------------+

Thanks for the upvotes ;-)

回复收藏 0 原文

流绪微梦 2025-01-04 06:45:59

在打印任何内容之前，请确保您的 PHP 文件的顶部有此内容。我可以将 latin_swedish_ci 放入 utf8 编码的网站，并且它编码正确。

header("Content-type: text/html;charset=UTF-8");

我还将其放在数据库连接之后（不确定这是否同样重要）：

mysql_query("设置名称'utf8'"); mysql_query("设置字符集'utf8'");

回复收藏 0 原文

忱杏 2025-01-04 06:45:59

对于阅读这篇文章的其他人来说，我发现添加这些 mysqld 配置行 - 如果您有权访问 mysql 服务器并且可以进行更改 - 解决了我的大引号问题。

http://dev.mysql.com/doc/refman/5.6 /en/charset-server.html

# Force UTF8 Charset Encoding
skip-character-set-client-handshake
collation_server=utf8_unicode_ci
character_set_server=utf8

我仔细检查了从 PHP 调用的 SQL（看起来很好），并且还从 GUI 中手动执行了带有大引号的插入/更新语句（效果很好），但从网络服务器仍然得到插入数据库的多控制字符。

我检查了 mysql 服务器变量，发现 latin1 是服务器和数据库的默认值（即使表/列是 UTF8）。一旦我添加了上面的行并刷新了发出更新语句的页面，大引号就正确插入了。我只能假设这与我们服务器的默认字符集 latin1 和 Web 服务器 mysql 库握手协商有关。

For what it's worth for anyone else coming accross this post, I found that adding these mysqld configuration lines - if you have access to the mysql server and can make changes - solved my problem with the curly-quotes.

http://dev.mysql.com/doc/refman/5.6/en/charset-server.html

# Force UTF8 Charset Encoding
skip-character-set-client-handshake
collation_server=utf8_unicode_ci
character_set_server=utf8

I had double-checked the SQL being called from PHP (which appeared fine), and also manually executed an insert/update statment with curly quotes from my GUI (which worked fine), but from the web server was still getting the multi-control characters inserted into the database.

I checked my mysql server variables and noticed latin1 was the default for the server, and the database (even though the table/columns were UTF8). Once I added the lines above and refreshed the page that issued the update statement, the curly quotes did insert correctly. I can only assume this had something to do with our server's default charset being latin1 and the web server mysql library handshake negotiating as such.

回复收藏 0 原文

怀念你的温柔 2025-01-04 06:45:59

正如其他人所说，首先解决以下两个问题：

将数据库转换为 UTF-8。
发送 UTF-8 HTTP 标头。

如果您在转换数据库后在命令行中遇到 “test” 问题仍然，请确保在运行 mysql 之前的 SSH Linux CLI...

export LANG=en_US.UTF-8

As stated by others, solve the two following problems first:

Convert your database to UTF-8.
Send UTF-8 HTTP headers.

If you are having problems with â€œtestâ€ in the command-line STILL, after converting the DB, then make sure to run this in your SSH linux CLI before running mysql...