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

Question

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

mysqlutf-8character-encodinglatin1

20

我即将进行一个繁琐且容易出错的任务，将数据库从Latin1转换为UTF-8。

目前，我只需要检查一下我存储在表中的数据类型，这将决定我应该采用什么方法来转换数据。

具体而言，我想检查一下我的Latin1列中是否有UTF-8字符，最好的方法是什么？如果只有少数行受到影响，那么我可以手动修复。

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

选项2.使用MySQL CHAR_LENGTH查找具有多字节字符的行？例如：SELECT name FROM clients WHERE LENGTH(name) != CHAR_LENGTH(name); 这样足够吗？

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

- dinie

3

由于LATIN1是一个8位字符集，所以您可能会无意中将UTF-8数据存储在LATIN1列中，但在错误的编码下它看起来就像一团糟。 - tadman

这就是我要说的。数据库坚信数据是LATIN1，但客户端仍然将其解释为UTF-8。你可能会无意中将UTF-8放入LATIN1数据库，但你却无法将LATIN1放入UTF-8，因为无效的UTF-8字符会被截断。而不存在无效的LATIN1字符。 - tadman

1

@Borealid 0xF0 0x53 不是有效的UTF8序列，这就是为什么这是相当可行的，但只能做到一定程度。 - tripleee

1

@triplee 真倒霉，应该选择其中一个30,000多个含糊不清的双字节序列！但我想你明白我的意思。有些字符不能是UTF-8，但许多Latin1的双字符序列也是有效的UTF-8双字节字符。 - Borealid

2

@tripleee 而字节序列0xC2A0，在UTF-8中是一个不间断的空格，在Latin1中则是一个Â后跟着一个不间断的空格？这个神奇的消失字符！ - Borealid

显示剩余4条评论

4个回答

10

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

迄今错误的连接：您一直在使用latin1编码不正确地连接到数据库，但已将UTF-8数据存储在数据库中（此时列的编码是无关紧要的）。这就是我在这里描述的情况。在这种情况下，修复很容易：通过latin1连接将数据库内容转储到文件中。这将把错误存储的数据转换为正确存储的UTF-8格式，就像过去一样（请阅读上述链接的文章获取详细信息）。然后，通过正确设置utf8连接将数据重新导入到数据库中，它将按照应该的格式进行存储。
迄今错误的列编码：通过utf8连接将UTF-8数据插入到latin1列中。在这种情况下，请忘记它，数据已经丢失。任何非latin1字符都应替换为?。
迄今一切顺利，从此添加UTF-8支持：您已经正确地将Latin-1数据存储在latin1列中，并通过latin1连接插入，但现在想扩展支持UTF-8数据。在这种情况下，只需将列编码更改为utf8。 MySQL将为您转换现有数据。然后，插入UTF-8数据时请确保数据库连接设置为utf8。

- deceze

如果多个客户端都在添加数据，并且其中一些人认为他们应该提交utf8，那么你会得到一个混乱的组合，基本上需要手动解决。这并不意味着您不能自动化部分过程，事实上，大多数情况可能可以在没有人类干预的情况下决定。 - tripleee

真的，但那时你就真的完全出问题了。在尝试回答这种情况之前，原帖作者需要提供更多有关实际问题的信息。 - deceze

对于情况1，适用于我的命令是mysqldump --default-character-set=latin1 -u user -p database。然后我必须进入转储文件并将SET NAMES latin1更改为utf8。然后重新导入转储文件，问题解决了。 - James

2

在 GitHub 上有一个脚本可帮助处理此类问题：mysql-convert-latin1-to-utf8。

- Patrick James McDougle

1

这个脚本对我非常有用，我做了一些改进使它更快、更灵活。我还有一个分支，可以将字符集转换为MySQL的“正确”utf8mb4字符集。 - Synchro

这个脚本运行成功了，但我仍然不明白它是如何工作的，需要花点时间去研究一下。从 latin1 到 utf8 的转换几乎没有什么问题，只需要在之后添加 mysql_set_charset("utf8"); 让 PHP 正确使用即可。 - Karthik T

OP问如何在Latin1列中检测UTF-8字符。据我所知，目前的mysql-convert-latin1-to-utf8脚本实际上并没有帮助到这一点。相反，它有一个用户可修改的$collationMap数组，其中指定了一组键值对的排序规则。对于每个排序规则与之匹配的列，脚本将盲目地假定其内容是使用与值的排序规则相对应的字符集编码的。脚本将更改列的排序规则（以及隐含的字符集）为后者，同时保留内容的二进制值。 - user82216

检测有点不可能。有人把方钉塞进圆孔里，然后问为什么拿出来时不是方形的...你可以从输出的数据中看出来，但必须仔细观察才能知道（或者识别常见字符序列，当其被解释为UTF-8时比拉丁字符序列更有可能）。 - Patrick James McDougle

0

我会创建数据库的转储并使用grep查找所有有效的UTF8序列。然后可以根据结果采取相应措施。关于如何识别无效的UTF8，stackoverflow 上有多个相关问题；你可以基本上反转逻辑。

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

- tripleee

1

这个答案包含了一个相当长的可能是不良组合的列表。链接 - Synchro

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- tadman · Accepted Answer

字符编码，和时区一样，是一个经常引起问题的源头。

你可以查找任何“高ASCII”字符，因为它们或者是LATIN1重音字符或符号，或者是UTF-8多字节字符的第一个字符。要区分它们并不容易，除非你有点作弊。

要确定正确的编码方式，你只需要选择两个不同版本进行视觉比较。以下是一个示例：

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