Geek asks and answers

MSSQL for MySQL migration - char problems with encoding UCS-2 surrogate pairs, how can I remove them from the MSSQL database?

I was instructed to migrate the Microsoft SQL Server 2005 database to MySQL 5.6 (both of these database servers are running locally), and I would really like the help.

-MSSQL the source database is encoded in latin1 (does it have an ISO 8859-1 character set?), But does not have any char / varchar fields (any nvarchar / nchar string field), so all this data must use the UCS-2 character set.

-MySQL target database wants UTF-8 character set

I decided to use a toolkit for database migration in the latest version of MySQL toolkit. at first it worked fine and migrated everything as expected. But I completely worked, encountering the characters of the UCS-2 surrogate pair in the MSSQL database.

The migration toolkit copy program did not provide a very useful error message: "Error converting character set to wsring: no error." He also did not provide any field / row information for the problematic data and could not execute fragments of 100 lines. Therefore, after searching 100 lines after the last successful insertion, I found that the problem was apparently caused by two UCS-2 characters in one of the nvarchar fields. They are listed as surrogate pairs in the UCS-2 character set. They were specifically the DBC0 and DC83 characters (I got this by looking at the binary data for the field and comparing the byte pairs (small number) with the data that were successfully transferred).

When this surrogate pair was removed from the MSSQL database, the row was successfully transferred to MySQL.

Here is the problem:

MSSQL ( - nvarchar), script ... - .

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

( , DC83), , , , ( ) . , ( , , ).

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

! , , , DC83. ? - ?

, , UCS-2, , .

, ( ~ 50 !), , , , ...

! , , - , .

+5
4

. 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)
0

, . , , , -, , .

MSSQL MySQL, - html- Sitecore CMS ( CMS - Drupal, btw).

, , Instagram-embedded. Instagram- , ( async., Et.c. - base64-css...), - ( iPhone Emoji). Emoji 4- , MySQL utf8 .

wbcopytables.exe ( GUI- MySQL Workbench)

wstring:

MySQL Workbench ( 5.something 6.x) , (, ):

: UCS-2 UTF-8 [MyDatabase]. [Dbo]. [MyTable] ( MyColumn). :...

- * * utf8mb4, emoji. .

: , , . Drupal.

, , , - script. , -. SQL Server, DAL #.NET, , Jon Skeet) - , UTF-16. . ( ).

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

" script.cmd", " NVARCHAR", "VARCHAR".

. VARCHAR, ... - script UNICODE (NVARCHAR).

+2

I had a very similar problem today, and I found that it was caused by empty strings, replaced them with NULL or a value that did not represent any data, and the migration worked fine.

+2
source

More articles:


All Articles