将一个JSON列转换为utf8mb4

Question

在一个 MySQL 插入请求中，我使用了（$text 由 $_POST 发送，并且列 MESSAGE 是 JSON 格式）：

$data = '{"mytext":".'$text'."}';

INSERT INTO xxxxx (MESSAGE) VALUES('$mytext');

我被以下问题困扰：

一些表情符号显示正常，但有些显示为“？”。

我在这里搜索答案，发现必须在数据库、表和列上设置utf8mb4。

问题是当我尝试以下内容时，其中包含我的注册消息：

ALTER TABLE xxxxx CHANGE column_name column_name JSON CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

以上的结果： “您的SQL语法存在错误；” 我认为这只可能在Varchar()列上发生。 您有任何想法如何使其工作吗？ 谢谢

- Bloob

---

你的代码存在 SQL 注入漏洞！这是一个巨大的安全风险！不要在实际应用中使用你的代码！ - cramopy

完整的错误信息是什么，它必须指向某些被认为不正确的特定部分（“附近...”）。 - deceze

精确的错误信息是：ERROR 1064 (42000)：您的SQL语法存在错误；请检查与您的MySQL服务器版本相对应的手册，以获取正确的语法使用方式，位于第1行附近的'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' > 这就是我认为将JSON列转换为utf8mb4不相关的原因。 - Bloob

与表的内容无关，它意味着 ... column_name JSON CHARACTER SET ... 是一个无效的查询。 - deceze

@deceze 当我执行 column_name varchar(xx) 时，它没有抛出任何错误，因此我认为这就是问题所在。 - Bloob

Answer 1

有些表情符号可以正常显示，但有些会显示问号。从“UTF-8字符的问题：我看到的不是我存储的内容”中找出导致“问号”的问题。具体包括：

1. 要存储的字节没有编码为utf8mb4，请修复此问题。 2. 数据库中的列不是CHARACTER SET utf8mb4，请修复此问题。（使用SHOW CREATE TABLE查看当前设置） 3. 此外，在读取期间检查连接是否为UTF-8。

如需进一步讨论，请提供以下信息：

1. ALTER之前的SHOW CREATE TABLE。（我怀疑VARCHAR当前是CHARACTER SET utf8。） 2. 完整的错误消息。（我猜它说“near 'CHARACTER...'”。） 3. 您正在使用的MySQL版本。

手册中写道：“MySQL使用utf8mb4字符集和utf8mb4_bin排序处理JSON上下文中使用的字符串。”我推断您不需要为数据类型JSON指定字符集或排序规则。

- Rick James

---

@danish_wani - 表面上看来这是一个简单的问题，但在实际操作中却很复杂。我不知道答案，我会进行一些研究。 - Rick James

@danish_wani - 我非常确定，至少在MySQL 8.0中，你被强制使用utf8mb4，不需要做任何事情。(我尝试了一个具有_不同_默认字符集的表格;它很顺利。然而，这可能不是唯一需要测试的内容。) - Rick James

是的，我的问题是将货币符号存储到默认字符集为latin1的MySQL数据库中，但大多数符号都会引发错误，然后我认为我可能需要更改字符集，但当我在不更改字符集的情况下将相同的货币符号数据插入json字段时，它可以正常工作并存储符号。 - danish_wani

@danish_wani - 看起来使用JSON数据类型的列被编码为utf8mb4（没有选择），而VARCHAR和TEXT允许设置字符集（如latin1）。 - Rick James

是的，那是真的。谢谢。感激不尽。 - danish_wani