在MySQL中，我该如何检查一个二进制字符串是否为UTF-8编码？

Question

我发现了一个Perl正则表达式，可以检查一个字符串是否为UTF-8编码（该正则表达式来自w3c网站）。

$field =~
  m/\A(
     [\x09\x0A\x0D\x20-\x7E]            # ASCII
   | [\xC2-\xDF][\x80-\xBF]             # non-overlong 2-byte
   |  \xE0[\xA0-\xBF][\x80-\xBF]        # excluding overlongs
   | [\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}  # straight 3-byte
   |  \xED[\x80-\x9F][\x80-\xBF]        # excluding surrogates
   |  \xF0[\x90-\xBF][\x80-\xBF]{2}     # planes 1-3
   | [\xF1-\xF3][\x80-\xBF]{3}          # planes 4-15
   |  \xF4[\x80-\x8F][\x80-\xBF]{2}     # plane 16
  )*\z/x;

但我不确定如何将它移植到MySQL，因为似乎MySQL不支持字符的十六进制表示，请参见这个问题。

你有什么想法可以将正则表达式移植到MySQL吗？ 或者你知道其他检查字符串是否有效UTF-8的方法吗？

更新： 我需要在MySQL上运行此检查，以便在服务器上纠正损坏的表。由于数据库大小约为1TB，我无法通过脚本传递数据。

- Piotr Czapla

Answer 1

我已经成功修复了我的数据库，使用的测试只适用于数据可以用一字节编码表示的情况，对我而言是latin1。

我利用了mysql在转换为latin1时将不是utf-8的字节更改为“？”的事实。

这是检查的样子：

SELECT (
         CONVERT(
           CONVERT(
              potentially_broken_column 
           USING latin1) 
         USING utf8))
       != 
       potentially_broken_column) AS INVALID ....

- Piotr Czapla

---

这仅适用于西欧语言，如英语、西班牙语等。如果其中有东方字符，则可能不起作用。 - Jesse Weigert

Answer 2

如果您控制此数据库的输入和输出，则应该能够验证您的数据在任一侧上是否为UTF-8，并根据需要实施约束。如果您正在处理一个您无法控制输入端的系统，则必须在提取数据后检查它，并可能在您选择的语言中进行转换（听起来像是Perl）。

数据库是一个非常好的存储设施，但不应过度用于其他应用程序。我认为这是一个地方，您应该让MySQL保留数据，直到您需要进一步处理它。

如果您想继续您的道路，请查看此MySQL手册页面：http://dev.mysql.com/doc/refman/5.0/en/regexp.html

正则表达式通常在各种语言之间非常相似（事实上，我几乎总是可以在JavaScript、PHP和Perl之间复制，只需进行少量调整即可），因此如果正则表达式有效，则应该能够轻松移植它。

祝你好运！

编辑：看看这篇Stack文章--考虑到您无法使用脚本处理数据，您可能需要使用存储过程：存储过程中的正则表达式

使用存储过程，您可以循环遍历数据并进行大量处理，而无需离开MySQL。第二篇文章将直接引用我列出的那篇文章，因此我认为您需要首先证明您的REGEX并使其正常工作，然后再研究存储过程。

- Shane

---

我无法移植字符的十六进制表示，例如\x61。有关更多详细信息，请参阅我所指的问题。 - Piotr Czapla

@Piotr Czapla - 你能告诉我你想要存储什么数据以及为什么需要以十六进制存储吗？这可能会帮助我或其他人找出问题所在。你使用哪种类型的字段来存储它？（文本|二进制|varchar）。此外，我正在扩展我的答案--看看这是否有帮助。 - Shane

真遗憾我没有以十六进制存储任何数据。我正在修复一个损坏的数据库，需要检查修复转换是否正常工作。为此，我想将问题中引用的正则表达式移植到mysql中。该正则表达式使用字符的十六进制表示来定义有效utf字符的值范围。而mysql不支持十六进制表示。我需要找到一种解决方法来解决这个问题。 - Piotr Czapla