mysql - MSSQL 到 MySQL 迁移 - UCS-2 代理项对的字符编码问题，如何从 MSSQL 数据库中删除这些问题？

Question

我的任务是将 Microsoft SQL Server 2005 数据库迁移到 MySQL 5.6(这些都是本地运行的数据库服务器)，非常感谢您的帮助。

-MSSQL 源数据库有 latin1 排序规则(所以 ISO 8859-1 字符集对吗？)但没有任何 char/varchar 字段(任何字符串字段都是 nvarchar/nchar)所以所有这些数据都应该使用 UCS -2 个字符集。

-MySQL目标数据库需要字符集UTF-8

我决定使用最新版本的 MySQL workbench 中的数据库迁移工具包。起初它工作正常并按预期迁移了所有内容。但是在 MSSQL 数据库中遇到 UCS-2 代理对字符时，我完全被绊倒了。

迁移工具包复制表程序没有提供非常有用的错误消息:“wstring 的字符集转换期间出错:无错误”。它还没有提供有关导致问题的数据的任何字段/行信息，并且会在 100 行的 block 内失败。因此，在上次成功插入后搜索 100 行后，我发现问题似乎是由其中一个 nvarchar 字段中的两个 UCS-2 字符引起的。它们在 UCS-2 字符集中被列为代理项对。它们具体是字符 DBC0 和 DC83(我是通过查看该字段的二进制数据并将字节对(小端)与成功迁移的数据进行比较得到的)。

当这个代理项对从 MSSQL 数据库中删除时，该行已成功迁移到 MySQL。

问题是:

我试图在一个测试 MSSQL 表中搜索这些字符(这个 chartest 表只是各种测试字符串和一个 nvarchar 字段)来准备一个替换脚本并不断得到奇怪的结果......我一定是做错了什么。

搜索

SELECT * FROM chartest WHERE text LIKE NCHAR(0xdc83)

将返回任何代理对字符(无论是否使用 DC83)，但显然，只有当它是该字段中唯一的字符(或对的一部分)时。这没什么大不了的，因为无论如何我都想删除这些实例(我不喜欢删除这样的数据，但我认为我们负担得起)。

搜索

SELECT * FROM chartest WHERE text LIKE '%' + (NCHAR(0xdc83))+ '%'

将返回每一行!不管字段中是否存在 unicode 字符，更不用说 DC83 字符了。有没有更好的方法来查找和替换这些字符？或者我应该尝试其他什么？

我也尝试过将目标数据库、表和字段字符集设置为 UCS-2，但似乎没有什么不同。

我还应该提到，此迁移使用的是实时数据(~50GB 数据库!)，而为其提供数据的站点之一已离线，因此对此的任何解决方案都需要快速运行...

如果有任何建议，我将不胜感激!如果我遗漏了任何信息，请告诉我。

Answer 1

我有这个错误，现在我找到了问题的根源。我很难找到答案，所以也许这对某些人有用，尽管我意识到，我的问题和解决方法可能无法解决匹配操作的原始问题。

我正在将数据从 MSSQL 迁移到 MySQL，迁移的内容是来自 Sitecore CMS 的 html 内容(顺便说一句， objective-c MS 是 Drupal)。

我发现，在转换数据库和命中包含 Instagram-embeds 的记录时出现此错误。 Instagram-embeds 的工作方式是将嵌入的帖子数据复制到嵌入代码(而不是异步加载等 - 甚至图像包含为 base64-css ...)，而年轻人现在倾向于在他们的图像描述中加入很多表情符号(使用带有表情符号键盘的 iPhone)。表情符号由 4 字节编码的字符表示，但 MySQL utf8 只允许 3 字节编码的 unicode 字符。

我最初运行 wbcopytables.exe(这是在 MySQL Workbench 中执行迁移向导的非 GUI 方式)时出现的错误是

Error during charset conversion of wstring: No error

但是将 MySQL Workbench 升级到最新版本(从 5.something 到 6.x)会使错误更具描述性，提示表和列(唉，不是行):

ERROR: Could not successfully convert UCS-2 string to UTF-8 in table [MyDatabase].[dbo].[MyTable] (column MyColumn). Original string: ...

无论如何 - 一个解决方案*可能*是使用允许表情符号的 utf8mb4。阅读更多 here .

但它看起来像是 a bad idea例如这样做我的 Drupal 案例。

所以 - 我最终得到的解决方案只是在我的迁移脚本中删除这些字符。为相关网站的用户保留这些内容毫无意义，因为它们无论如何都在网页上显示为矩形。由于您无法在 SQL Server 中使用正则表达式进行搜索和替换，因此我使用 DAL 和 c# .NET 处理了数据，并找到了帮助 here (非常感谢，Jon Skeet)——事实证明有一个正则表达式模式可以匹配 UTF-16 中的代理对的一半。请参阅下文(并在需要时使用另一种语言的模式)。

var noUcs2SurrogatePairsString = Regex.Replace(stringWithUcs2SurrogatePairs, @"\p{Cs}", string.Empty);