如何修复“Incorrect string value”错误？

Question

在发现一个应用程序由于错误的字符串值而倾向于丢弃随机电子邮件后，我检查了许多文本列，使用utf8列字符集和默认列排序(utf8_general_ci)，以便它能够接受它们。这样做修正了大部分错误，并使应用程序在遇到非拉丁语系的电子邮件时停止收到sql错误。

尽管如此，一些电子邮件仍然导致程序出现错误的字符串值：(Incorrect string value: '\xE4\xC5\xCC\xC9\xD3\xD8...' for column 'contents' at row 1)

内容列是一个MEDIUMTEXT数据类型，使用utf8列字符集和utf8_general_ci列排序。该列中没有可以切换的标志。

请注意，我不想触摸或甚至查看应用程序源代码，除非绝对必要：

• 是什么导致了这个错误？(是的，我知道这些电子邮件充满了随机垃圾，但我认为utf8会非常宽容)
• 我该如何解决它？
• 这样的解决方案可能会产生什么影响？

我考虑的一件事是切换到一个带有二进制标志的utf8 varchar([某个大数])，但我对MySQL并不熟悉，也不知道这样的解决方案是否可行。

- Brian

---

死因分析：RichieHindle的解决方案解决了这个问题，在运行期间未引入任何其他问题。 它可能有点不太正常，但它起作用了，并且使我避免了接触那些我不完全理解的第三方软件。 现在，我们已经升级到一个新版本的软件/模式，可以正确地处理所有这些编码问题（而且是足够新的，以至于实际上得到支持），这使这个“hack”不再必要。 - Brian

所有那些答案都没有切入要点。这个优雅的解决方案对我很有用：https://dba.stackexchange.com/a/21684/259488。你可以通过几行SQL将编码更改为`utf8mb4`，以修复数据库或表中的问题。 - Nikolas

Answer 1

以下是更新的答案:

在问题被提出时，“UTF8”在MySQL中表示的是“utf8mb3”。与此同时，添加了“utf8mb4”，但据我所知MySQL的“UTF8”并未切换为表示“utf8mb4”。

这意味着，如果你需要使用“utf8mb4”（而你应该使用它），你需要明确指出。

我将保留这里，而不仅仅是编辑答案，以明确说“UTF8”仍然存在差异。

原始内容：

我不建议使用Richie的答案，因为这会破坏数据库内部的数据。你不会解决你的问题，只是试图“隐藏”它，并且无法使用关键的数据库操作来处理这些垃圾数据。

如果你遇到这个错误，要么你发送的数据不是UTF-8编码，要么你的连接不是UTF-8编码。首先，请验证数据源（文件等）是否确实是UTF-8编码。

然后，检查你的数据库连接，在连接后应该执行以下操作：

SET NAMES 'utf8mb4';
SET CHARACTER SET utf8mb4;

接下来，请验证存储数据的表是否具有 utf8mb4 字符集：

SELECT
  `tables`.`TABLE_NAME`,
  `collations`.`character_set_name`
FROM
  `information_schema`.`TABLES` AS `tables`,
  `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` AS `collations`
WHERE
  `tables`.`table_schema` = DATABASE()
  AND `collations`.`collation_name` = `tables`.`table_collation`
;

最后，检查您的数据库设置：

mysql> show variables like '%colla%';
mysql> show variables like '%charac%';

如果源、传输和目标都是utf8mb4，你的问题就解决了;)

- nico gawenda

---

@Kariem: 这很奇怪，因为该设置由SET NAMES命令覆盖，这相当于调用SET character_set_client、SET character_set_results、SET character_set_connection。 http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html - nico gawenda

第二个命令应该是 SET CHARACTER SET utf8（而不是 CHARACTER_SET）。 - Coder

虽然这个答案有助于调查问题，但它并没有回答如何解决它。我看到的是 "latin1" 而不是 "utf-8"。 - Vanuan

这篇答案在解释问题方面非常出色，但在详细说明解决方案方面非常差（这正是OP所要求的）。@nicogawenda：所有需要运行的SQL查询是什么，以完全解决问题？如何修复所有现有数据？ - Clint Eastwood

如果源、传输和目标都是UTF-8，你的问题就解决了；）这对我来说很管用。 - therealbigpepe

Answer 2

MySQL的UTF-8类型实际上并不是完全的UTF-8 - 它只使用每个字符最多三个字节，并且仅支持基本多文种平面（即没有表情符号、天文学平面等）。

如果您需要存储来自更高的Unicode平面的值，则需要使用utf8mb4编码。

- moeffju

---

我认为这可能是最好的解决方案。升级到5.5版本，并在上述答案中将utf8替换为utf8mb4。我正在插入来自Twitter的utf8数据，其中包含需要4个字节的表情符号或其他字符。 - rmarscher

假设我们不打算升级到5.5版本，我们该如何抑制错误？ - User

我滚动了太远才找到这个最有用的答案。 - deqi

原问题已经10年了。让大家知道，MySQL的utf8编码不是真正的utf8编码。请使用utf8mb4！MariaDB也是如此。否则你将无法留下欣喜的泪水。 - Liam

这是最好的修复。即使在2021年！ - Displee

Answer 3

表格和字段的编码不正确，但是您可以将它们转换为UTF-8。

ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

- Jiayu Wang

---

我认为这是所有答案中正确的一个。我有两个表，每个表都有一个utf8 varchar格式。其中一个出现了错误，另一个没问题。即使我使用“update select”从“好”的utf8列中复制到另一个表，仍然会出现相同的错误。这是因为这两个表是在不同版本的MySQL中创建的。 - AiShiguang

这个答案为什么在下面，应该在顶部。 - Sagun Shrestha

这个很有帮助，它告诉你应该尝试什么，而不是可能出了什么问题。 - Victor Di

对我来说，只需要最后一个查询就足够了（更改用户输入所在的列），尽管我将其设置为... CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci而不是utf8，因为当前在此之上的答案：https://dev59.com/7HM_5IYBdhLWcg3w6X1e#24559308 - Luc

这真的解决了问题！！非常感谢 - Grumpy Civet

Answer 4

"\xE4\xC5\xCC\xC9\xD3\xD8" 不是有效的 UTF-8 编码。使用 Python 进行测试：

>>> "\xE4\xC5\xCC\xC9\xD3\xD8".decode("utf-8")
...
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 0-2: invalid data

如果您想避免数据库中的解码错误，cp1252编码（又名“Windows-1252”又名“Windows西欧”）是最宽容的编码 - 每个字节值都是有效的代码点。

当然，它将不再理解真正的UTF-8或任何其他非cp1252编码，但看起来您并不太关心这一点？

- RichieHindle

---

你的意思是，“当然它再也无法理解真正的UTF-8了”对吗？ - Brian

如果你告诉它你要使用cp1252编码，然后实际上给它提供了UTF-8的café，它会将其误解为café。它不会崩溃，但是会误解高位字符。 - RichieHindle

@Richie：数据库可以随意称呼数据，但如果抓取数据的 PHP 代码将其塞到字符串中，那么这并没有太大区别……不是吗？我并没有看到理解 UTF-8 缺失对结果造成什么影响。 - Brian

@Brian：不，你说得对。它会有影响的时候是在数据库内部，例如如果你在SQL语句中使用了ORDER BY子句，那么当出现非ASCII字符时，排序就会出现问题。 - RichieHindle

请取消标记此答案为解决方案，隐瞒错误不是解决问题的办法。从您的汽车中拆除过热的灯泡，然后您就会看到结果。 - David Vartanian

Answer 5

今天我通过将列更改为“LONGBLOB”类型来解决了这个问题，该类型存储原始字节而不是UTF-8字符。

这样做的唯一缺点是您必须自己处理编码。如果您应用程序的一个客户端使用UTF-8编码，而另一个客户端使用CP1252，则可能会发送带有不正确字符的电子邮件。为避免这种情况，请在所有应用程序中始终使用相同的编码（例如UTF-8）。

有关TEXT / LONGTEXT和BLOB / LONGBLOB之间差异的更多详细信息，请参阅此页面http://dev.mysql.com/doc/refman/5.0/en/blob.html。网上还有很多其他讨论这两个类型的文章。

- frankshaka

---

这个解决方案似乎是最简单的方法。我尝试了几种其他编码方式，但没有成功。 - Simeon Abolarinwa

Answer 6

首先检查你的 default_character_set_name 是否为 utf8。

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "DBNAME";

如果结果不是UTF-8，则必须转换数据库。首先，您必须保存转储。

要将指定数据库中所有表的字符集编码更改为UTF-8，请在命令行中键入以下命令。请使用数据库名称替换DBNAME：

如果结果不是 utf8，你必须转换你的数据库。首先，你必须保存一个 dump。

要将指定数据库中所有表的字符集编码更改为 UTF-8，请在命令行中输入以下命令。请用实际的数据库名称替换 DBNAME：

mysql --database=DBNAME -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' | mysql --database=DBNAME

要将数据库本身的字符集编码更改为UTF-8，请在mysql提示符下输入以下命令。用数据库名称替换DBNAME：

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

现在您可以重试将UTF8字符写入数据库。当我尝试将200,000行CSV文件上传到数据库时，这个解决方案对我很有帮助。

- Babacar Gningue

Answer 7

尽管您的字符集排序设置为utf8_general_ci，但我怀疑数据库、表甚至列的字符编码可能不同。

ALTER TABLE tabale_name MODIFY COLUMN column_name VARCHAR(255)  
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

- Sameera Prasad Jayasinghe

Answer 8

通常情况下，当您向具有不兼容编码/排序规则的列插入字符串时会发生这种情况。

我在使用触发器时遇到了这个错误，原因是它继承了服务器的排序规则。而mysql的默认排序规则（至少在Ubuntu上）是latin-1和瑞典排序规则。 即使我已将数据库和所有表设置为UTF-8，我仍需要设置my.cnf：

/etc/mysql/my.cnf :

[mysqld]
character-set-server=utf8
default-character-set=utf8

这里需要列出所有的UTF8触发器：

select TRIGGER_SCHEMA, TRIGGER_NAME, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION from information_schema.TRIGGERS

这里列出的变量中，有些还应该使用utf-8-*编码（不能使用latin-1或其他编码）：

show variables like 'char%';

- Ondra Žižka

Answer 9

那个错误的意思是你可能使用了错误的编码来输入字符串（例如，你正在尝试将 ISO-8859-1 编码的字符串输入到 UTF-8 编码的列中），或者该列不支持你正在尝试输入的数据。

实际上，后一种问题是由 MySQL UTF-8 实现造成的，它仅支持需要在 UTF-8 中表示为 1-3 字节的 UNICODE 字符。有关详细信息，请参见 "Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC? 。解决方法是使用列类型 utf8mb4 而不是名为 utf8 但实际上并未完全支持所有 UTF-8 的类型。前者才是所有 UTF-8 字符都应使用的正确类型。

- Mikko Rantalainen

Answer 10

我遇到了一个类似的错误 (Incorrect string value: '\xD0\xBE\xDO\xB2. ...' for 'content' at row 1)。 我尝试将列的字符集更改为utf8mb4，然后错误变成了'Data too long for column 'content' at row 1'。
结果发现mysql显示了错误的错误。我将列的字符集改回utf8，并将列的类型更改为MEDIUMTEXT。 然后错误消失了。
希望能对其他人有所帮助。
顺便说一句，在同样的情况下，MariaDB（我也在那里测试了相同的INSERT）只是截断了文本而没有报错。

- AVKurov

---

MySQL太过了。我尝试了很多方法，发现这个版本不支持4字节的UTF-8解码，一直在努力理解是什么原因导致的。显然，更改类型是答案，是一个即时的解决方案。 - Liza