在mysql中查找非UTF-8数据

Question

我有一个包含客户输入数据的MySQL数据库。

告诉客户他们的数据应该只使用UTF-8进行编码，但目前并没有强制执行，一些人正在使用未经UTF-8编码的字符。

我们打算添加一些检查，但是如何检测到未使用UTF-8编码的数据呢？是否有SQL或MySQL查询（或管理员命令）可以显示未经UTF-8编码的数据？

- Bret

Answer 1

你可以通过比较列的字节长度和字符长度来检查（非）UTF-8数据的存在，例如：

SELECT * FROM MyTable
WHERE LENGTH(MyColumn) <> CHAR_LENGTH(MyColumn)

多字节字符的长度（以字节为单位）较长，因此您需要查找不满足该条件的位置。

请注意，MySQL的utf8字符集并不是真正的Unicode UTF-8，因为它每个字符只支持最多3个字节。如果您的MySQL版本高于5.5.3，您可以使用utf8mb4来获得每个字符4个字节的支持。

- dartonw

---

在我的情况下，使用SELECT * FROM TABLE WHERE length(COLUMN) > char_length(COLUMN)非常有效，并返回了没有utf8的条目。 - Bret

我不认为这是正确的。UTF-8可以用1到4个字节编码字符，所以通过比较字符长度和字节长度来揭示非UTF-8数据是如何做到的呢？每个字符一个字节只包含（大致上）ASCII字符，所以你在这里回答的问题是如何找到非ASCII字符，而不是非UTF-8字符。我有什么地方理解错了吗？ - goron

这是不正确的 - 它将标记任何包含非ASCII字符的文本。虽然是有效的UTF8编码，但不是ASCII编码 - 就像@goron在2016年警告的那样。 - Mikhail T.

那么，为了进一步阐述这个错误答案，LENGTH('this is a test') <> CHAR_LENGTH('this is a test') 怎样被发现是没有使用UTF-8编码的呢？该字符串在LENGTH()和 CHAR_LENGTH() 中返回的结果是相同的。CHAR_LENGTH() 返回参数中的字符数，而LENGTH() 返回一个字符串的字节长度，在只包含ASCII编码字符的情况下，这两者是相同的。 - Hannah Vernon

@HannahVernon 你说得没错，但这不是问题所在。楼主想知道是否存在不在UTF-8中的字符。纯ASCII字符在Latin-1和UTF-8中的编码方式相似，但扩展ASCII字符的编码方式不同。例如，在Latin-1中，á的编码为0xE1，而在UTF-8中为0xC3A1，长度不同。 - mustaccio

@mustaccio 但问题不是关于“扩展ASCII字符”，而只是关于是否使用UTF-8编码。 - Lamak

@mustaccio - 是的，除非答案只返回长度和字符长度不同的行，但是原帖希望返回以ASCII编码的行。this肯定是以ASCII编码的，并且具有相同的长度和字符长度，除非我理解错了。 - Hannah Vernon

对于纯ASCII字符，任何编码都等同于UTF-8，而对于其他字符，长度会有所不同。如果列值只包含纯ASCII字符，则与UTF-8相同。this在Latin-1和UTF-8中使用相同的字节序列表示。而thát则不是，这正是OP想要解决的问题。"未以UTF-8编码"并不等同于"以ASCII编码"的问题。 - mustaccio

Answer 2

这将仅选择不是有效的UTF-8的字段：

SELECT * FROM table
WHERE CONVERT(field USING binary) RLIKE '([\\xC0-\\xC1]|[\\xF5-\\xFF]|\\xE0[\\x80-\\x9F]|\\xF0[\\x80-\\x8F]|[\\xC2-\\xDF](?![\\x80-\\xBF])|[\\xE0-\\xEF](?![\\x80-\\xBF]{2})|[\\xF0-\\xF4](?![\\x80-\\xBF]{3})|(?<=[\\x00-\\x7F\\xF5-\\xFF])[\\x80-\\xBF]|(?<![\\xC2-\\xDF]|[\\xE0-\\xEF]|[\\xE0-\\xEF][\\x80-\\xBF]|[\\xF0-\\xF4]|[\\xF0-\\xF4][\\x80-\\xBF]|[\\xF0-\\xF4][\\x80-\\xBF]{2})[\\x80-\\xBF]|(?<=[\\xE0-\\xEF])[\\x80-\\xBF](?![\\x80-\\xBF])|(?<=[\\xF0-\\xF4])[\\x80-\\xBF](?![\\x80-\\xBF]{2})|(?<=[\\xF0-\\xF4][\\x80-\\xBF])[\\x80-\\xBF](?![\\x80-\\xBF]))';

（正则表达式来自https://stackoverflow.com/a/11709412/323407。 需要PCRE正则表达式支持。MySQL默认不支持，但MariaDB 10.0.5+支持。对于MySQL，你可以使用lib_mysqludf_preg。）

（不确定这是否符合问题的要求 - 如果有人将他们的信息存储在非UTF-8编码中，但恰好输出的字节序列是有效的UTF-8，那么这并不能解决这个问题（我认为没有100%可靠的解决方案 - 如果你知道具体的编码和语言，也许可以寻找常见的损坏字符序列）。但它对于查找肯定不是UTF-8的输入很有用。）

（这假设文本以二进制等格式存储，其中无效的UTF-8甚至可能存在。）

- Tgr