如何检测 Latin1 编码列中的 UTF-8 字符 - MySQL

Question

我即将承担将数据库从 Latin1 转换为 UTF-8 的繁琐且充满陷阱的任务。

此时，我只想检查表中存储的数据类型，因为这将确定我应该使用什么方法来转换数据。

具体来说，我想检查 Latin1 列中是否有 UTF-8 字符，最好的方法是什么？如果只有几行受到影响，那么我可以手动修复此问题。

选项 1. 执行 MySQL 转储并使用 Perl 搜索 UTF-8 字符？

选项 2. 使用 MySQL CHAR_LENGTH 查找包含多字节字符的行？ 例如 从客户端中选择名称 WHERE LENGTH(name) != CHAR_LENGTH(name); 这够了吗？

目前我已将 Mysql 客户端编码切换为 UTF-8。

Answer 1

字符编码与时区一样，是一个持续存在的问题根源。

您可以做的是查找任何“高位 ASCII”字符，因为这些字符要么是 LATIN1 重音字符或符号，要么是 UTF-8 多字节字符的第一个。除非你作弊，否则辨别差异并不容易。

要确定哪种编码是正确的，您只需 SELECT 两个不同的版本并进行直观比较。这是一个例子：

SELECT CONVERT(CONVERT(name USING BINARY) USING latin1) AS latin1, 
       CONVERT(CONVERT(name USING BINARY) USING utf8) AS utf8 
FROM users 
WHERE CONVERT(name USING BINARY) RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')

这变得异常复杂，因为 MySQL 正则表达式引擎似乎忽略了诸如 \x80 之类的内容，并且需要使用 UNHEX() 方法来代替。

这会产生如下结果：

latin1                utf8
----------------------------------------
Björn                Björn

Answer 2

由于您的问题并不完全清楚，让我们假设一些情况：

1. 迄今为止错误的连接：您一直使用 latin1 编码错误地连接到数据库，但在数据库中存储了 UTF-8 数据（在这种情况下，列的编码无关紧要）。这就是我在此处描述的情况。在这种情况下，很容易修复：通过 latin1 连接将数据库内容转储到文件中。这会将不正确存储的数据转换为不正确存储的 UTF-8，这是迄今为止的工作方式（请阅读前面链接的文章以了解详细信息）。然后，您可以通过正确设置的 utf8 连接将数据重新导入数据库，并且它将按应有的方式存储。
2. 迄今为止错误的列编码：UTF-8 数据通过 utf8 连接插入到 latin1 列中。那样的话就算了，数据就消失了。任何非 latin1 字符都应替换为 ?。
3. 到目前为止，一切都很好，从此添加了对 UTF-8 的支持： 您已将 Latin-1 数据正确存储在 latin1 列中，并通过 latin1 连接插入，但希望将其扩展为也允许 UTF-8 数据。在这种情况下，只需将列编码更改为 utf8 即可。 MySQL 将为您转换现有数据。然后，当您插入 UTF-8 数据时，只需确保您的数据库连接设置为 utf8 即可。

Answer 3

github 上有一个脚本可以帮助解决此类问题。

Answer 4

我将创建数据库转储并查找所有有效的 UTF8 序列。从哪里得到它取决于你得到什么。 SO 有多个关于识别无效 UTF8 的问题；你基本上可以颠倒逻辑。

编辑：基本上，任何完全由 7 位 ASCII 组成的字段都是安全的，任何包含无效 UTF-8 序列的字段都可以假定为 Latin-1。剩余的数据应该被检查 - 如果你幸运的话，一些明显的替换将修复绝对多数（用 Latin-1 ö 替换 ö 等）。