Миграция от MSSQL към MySQL - проблеми с кодирането на char със сурогатни двойки UCS-2, как мога да ги премахна от MSSQL база данни?

Имах тази грешка и сега открих източника на проблема. Трудно ми беше да разбера, така че може би това ще бъде полезно за някого, въпреки че осъзнавам, че моят проблем и заобиколното решение може да не са съвпадащи с първоначалния проблем на операционната система.

Мигрирам данни от MSSQL към MySQL и съдържанието, което се мигрира, е html съдържание от Sitecore CMS (целевата CMS е Drupal, между другото).

Открих, че получавам тази грешка, когато преобразувам базата данни и натискам записи, които съдържат Instagram-embeds. Instagram-вгражданията работят по начин, че вградените данни за публикация се копират в кода за вграждане (вместо да се зареждат асинхронно и т.н. - дори изображението е включено като base64-css...), и младите хора в днешно време са склонни да поставят много емотикони в своите описания на изображения (използвайки своите iPhone с Emoji клавиатура). Емотикони са представени от 4-байтови кодирани знаци, но MySQL utf8 позволява само 3-байтови кодирани уникод знаци.

Първоначалната ми грешка при стартиране на wbcopytables.exe (което е не-GUI начин за извършване на съветника за мигриране в MySQL Workbench) беше

Грешка по време на преобразуване на набор от знаци на wstring: Няма грешка

но надграждането на MySQL Workbench до най-нова версия (от 5.something на 6.x) прави грешката малко по-описателна, подсказвайки таблица и колона (уви, не ред):

ГРЕШКА: Не можа успешно да се преобразува UCS-2 низ в UTF-8 в таблица [MyDatabase].[dbo].[MyTable] (колона MyColumn). Оригинален низ: ...

Както и да е - решение *може* да бъде използването на utf8mb4, което би позволило емотикони. Прочетете повече тук.

Но изглежда, че е лоша идея да правите това напр. моят случай с Drupal.

И така - решението, което намерих, беше просто да премахна тези знаци в моя скрипт за мигриране. Няма смисъл да ги пазите за потребителите на въпросния сайт, тъй като те така или иначе се показват като правоъгълници на уеб страницата. Тъй като не можете да търсите и заменяте с регулярен израз в SQL Server, обработих данните с помощта на DAL и c# .NET и намерих помощта тук (много благодаря, Jon Skeet) - оказва се, че има шаблон за регулярен израз за съпоставяне на едната половина от сурогатна двойка в UTF-16. Вижте по-долу (и използвайте модела на друг език, ако е необходимо).

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

Днес имах много подобен проблем и открих, че е причинен от празни низове, замених ги с NULL или стойност, която не представлява данни, и миграцията работи добре.

Реших просто редактиране на „скрипт за импортиране на данни.cmd“, където той чете колони „Като NVARCHAR“, като замених тези само с „VARCHAR“.

Забележка: Колоните на моята таблица вече бяха тип VARCHAR, така че... по някаква глупава причина скриптът за миграция неправилно ги прехвърли към тип UNICODE (NVARCHAR).

Този проблем вече е решен. Използвах предложението на потребител Remus Rusanu тук за намиране на редовете с тези заместващи двойки знаци използвайки CHARINDEX и са решили да използват SUBSTRING, за да изключат проблемните знаци като така:

UPDATE test
SET a = SUBSTRING(a,  1,   (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 - 1) -- string before the unwanted character
+ SUBSTRING(a, (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 +1, LEN(a) ) -- string after the unwanted character
WHERE CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000))) % 2 = 1 -- only odd numbered charindexes (to signify match at beginning of byte pair character)