问题描述
我正在使用链接服务器将数据从MySQL复制到SQL Server。
SELECT comment FROM openquery(my_linked_server, 'SELECT comment FROM search_data');
MySQL表列中的文本为xxx ?? xxx。当我在SQL Server中收到它时,它是xxx e?¤? xxx。MySQL表是utf8mb4,我已经为链接服务器设置了ODBC配置以使用它。我正在使用MySQL ODBC 5.3.13
任何建议都将不胜感激。SQL Server版本是2016,我已经看到了要放的例子
select N'??'
等,但不知道如何将其应用于上述查询。
Fin???似乎是4个字符
e = u00f0 , dec = 240 ? = u0178 , dec = 276 ¤ = u00a4 , dec = 164 ? = u02dc , dec = 732
??=ud83e,dec=55358
有趣的是,这根本不起作用
select nchar(unicode(N'??')),unicode(N'??')
返回?符号
推荐答案
我做了一个解决方案并发布,这样其他人就不会花一整天的时间做同样的事情
select ab_test.dbo.GetEmojisInString('e?‘?e?’–e?¤·a€a????e???e?±a€e?’?e??‰a¤e?±a€e?‘¤e?¤?e?¤£e?‘e?’?a??e?±a€e?e?’?e???e??‰e?¤|a€a????e???e??1e?‘e?±a€e?‰e???e??e?¤|a€a?€??e??e???e?±a€e??€e???e???e?±a€e?‘"e??¢e??’e?¤3e???')
将返回
???????♂???????????????????????????????????????♂???????????????????♀???????????????????????????
下面有5个函数,可能不是完美的,也可能是更短/更好的方法,但这是有效的。如果有任何错误,请让我知道。
注意:我必须拆分两个数据库,因为排序规则需要有_CS,而在我的解决方案中,下面的bi_库数据库我无法更改,因为数据库已锁定,因此目前只创建了一个ab_test数据库。
USE [bi_library] GO CREATE FUNCTION [dbo].[GetDecimalFromOtherBase] ( @p_in_value varchar(100), @p_from_base int -- ie 16 for hex, 8 for octal, 2 for bin ) returns int as begin declare @l_in_value varchar(100) = reverse(@p_in_value) -- spin backwards as maths works in easier this way declare @l_from_base varchar(100) = @p_from_base--@p_from_base --= @p_in_value declare @l_pos int = 1 declare @l_char char(1) declare @l_val int = 0 declare @l_total int = 0 while @l_pos<= len(@l_in_value) begin set @l_char = substring(@l_in_value,@l_pos,1) if isnumeric(@l_char)=0 begin set @l_val = ascii(@l_char)-55 -- convert A to 10, F to 15 etc end else begin set @l_val = @l_char end set @l_total = @l_total + (power(@l_from_base,@l_pos-1)*@l_val) set @l_pos=@l_pos+1 end return @l_total end GO CREATE FUNCTION [dbo].[GetOtherBaseFromDecimal] ( @p_in_value int, @p_to_base int -- ie 16 for hex, 8 for octal, 2 for bin ) returns varchar(100) as begin -- convert decimal to other base declare @l_dec int = @p_in_value declare @l_ret_str varchar(100) = '' declare @l_rem int = 0 declare @l_rem_char char(1) = '?' while @l_dec > 0 begin set @l_rem = @l_dec % @p_to_base if @l_rem >= 10 begin set @l_rem_char = char(55+@l_rem) end else begin set @l_rem_char = cast(@l_rem as varchar) end set @l_ret_str = @l_ret_str + @l_rem_char set @l_dec = @l_dec / @p_to_base end return reverse(@l_ret_str) end GO CREATE FUNCTION [dbo].[GetBaseFromOtherBase] ( @p_in_value varchar(100), @p_in_base bigint, -- ie 16 for hex, 8 for octal, 2 for bin @p_to_base bigint -- ie 16 for hex, 8 for octal, 2 for bin ) returns varchar(100) as begin return bi_library.dbo.GetOtherBaseFromDecimal(bi_library.dbo.GetDecimalFromOtherBase(@p_in_value,@p_in_base),@p_to_base) end GO USE [ab_test] GO ALTER function [dbo].[GetEmojisInString] (@p_in_string nvarchar(max)) returns nvarchar(max) as begin declare @l_string varchar(1000) = @p_in_string --'a??e??e?’?e?¤·a€a????e?¤3e?±a€e?‘"e?±a€e??€e?±a€e?‰e???e?’?e?¤?e??‰e?‘?e?¤|a€a?€??e?±a€e?e?’–e??’e???e??e?‘e?¤|a€a????e?‘e?±a€e?‘¤e?±a€e?’?e???e???e???e???e??¢e???e???e??1e??‰e?¤£a¤e?¤·a€a?€??' declare @l_pos int = 1 declare @l_char varchar(1) declare @l_cont_extended_ascii int = 0 declare @l_byte1_hex varchar(2) declare @l_byte2_hex varchar(2) declare @l_byte3_hex varchar(2) declare @l_byte4_hex varchar(2) declare @l_hex_char varchar(2) declare @l_str nvarchar(max) = '' declare @l_dec_value_found int while @l_pos <= len(@l_string) begin set @l_char = substring(@l_string,@l_pos,1) --print(ascii(@l_char)) if ascii(@l_char)>=128 begin set @l_cont_extended_ascii = @l_cont_extended_ascii+1 --print(@l_char) set @l_hex_char = bi_library.dbo.GetOtherBaseFromDecimal(ascii(@l_char),16) if @l_cont_extended_ascii = 1 begin set @l_byte1_hex = @l_hex_char --print('set byte 1') end else if @l_cont_extended_ascii = 2 begin --print('set byte 2') set @l_byte2_hex = @l_hex_char set @l_dec_value_found = bi_library.dbo.GetDecimalFromOtherBase( reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte1_hex,16,2)),1,6))+ reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte2_hex,16,2)),1,6)) ,2) if @l_dec_value_found between 128/*U+0080*/ and 2047/*U+07FF */ begin --print('2 byte emoji found') set @l_str = @l_str+coalesce(nchar(@l_dec_value_found),'?') set @l_cont_extended_ascii = 0 end end else if @l_cont_extended_ascii = 3 begin --print('set byte 3') set @l_byte3_hex = @l_hex_char set @l_dec_value_found = bi_library.dbo.GetDecimalFromOtherBase( reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte1_hex,16,2)),1,4))+ reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte2_hex,16,2)),1,6))+ reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte3_hex,16,2)),1,6)) ,2) if @l_dec_value_found between 2048/*U+0800*/ and 65535/*U+FFFF*/ begin --print('3 byte emoji found') set @l_str = @l_str+coalesce(nchar(@l_dec_value_found),'?') set @l_cont_extended_ascii = 0 end --print(@l_str) end else if @l_cont_extended_ascii = 4 begin set @l_byte4_hex = @l_hex_char set @l_dec_value_found = bi_library.dbo.GetDecimalFromOtherBase( reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte1_hex,16,2)),1,3))+ reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte2_hex,16,2)),1,6))+ reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte3_hex,16,2)),1,6))+ reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte4_hex,16,2)),1,6)) ,2) if @l_dec_value_found between 65536/*U+10000*/ and 1114111/*U+10FFFF*/ begin --print('4 byte emoji found') set @l_str = @l_str+coalesce(nchar(@l_dec_value_found),'?') set @l_cont_extended_ascii = 0 end else begin --print('out of range byte emoji found') set @l_str = @l_str+@l_char end --print(@l_str) --end set @l_cont_extended_ascii = 0 end end else begin --print('snapping') set @l_str = @l_str+@l_char set @l_cont_extended_ascii = 0 --print(@l_str) end set @l_pos = @l_pos+1 end --print(@l_str) return @l_str end CREATE function [dbo].[HasEmojisInString] (@p_in_string nvarchar(max)) returns int as begin declare @l_string_emojified varchar(1000) set @l_string_emojified = dbo.GetEmojisInString(@p_in_string) if @l_string_emojified <> @p_in_string begin return 1 end return 0 end GO