将 MSSQL 字符串转换为十六进制并在 MySQL 中取消十六进制值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18446861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert a MSSQL String to Hex and unhex the value in MySQL
提问by Stefan Brendle
I want to convert a string in Microsoft SQL to hexdecimal value, for example this:
我想将 Microsoft SQL 中的字符串转换为十六进制值，例如：
declare @b varbinary(max)
set @b = (Select cast('Ali' as varbinary))
select cast(@b as varchar(max))
select @b
It returns:
它返回：
This is great! But know I want to unhex the exact string in MySQL:
这很棒！但是知道我想对 MySQL 中的确切字符串进行 unhex：
So in MySQL the string looks a bit different from the string I get from MSSQL. Maybe I could do a string replace or something
所以在 MySQL 中，字符串看起来与我从 MSSQL 得到的字符串有点不同。也许我可以做一个字符串替换之类的
But with a more complex string it even varys between mssql and mysql:
但是对于更复杂的字符串，它甚至在 mssql 和 mysql 之间有所不同：
-- Same procedure as before, just tried another string: --
-- 和以前一样的过程，只是尝试了另一个字符串：-
MSSQL-String: 0x53414D31302F32303130E4F6FCDF5C2A23E92D656E64657C3C6469762073
MySQL-String: 53414D31302F32303130C3A4C3B6C3BCC39F2A23C3A92D656E
The beginning is the same (maybe because of the SAM), but it seems to be a problem with the special chars ... :-(
开头是一样的（可能是因为SAM），但似乎是特殊字符的问题...... :-(
回答by Andomar
The SQL Server and MySQL hex encodings start to diverge at position 11. The character at that position is ?, the first non-ascii character . So there is probable cause to believe that each database uses a different encoding.
SQL Server 和 MySQL 十六进制编码在位置 11 处开始出现分歧。该位置的字符是?，第一个非 ascii 字符 。所以有可能的理由相信每个数据库使用不同的编码。
The MySQL encoding is UTF-8
MySQL 编码为 UTF-8
The encoding of ?in UTF-8 is 0xC3A4, so that's what MySQL uses. This is confirmed by the utf-8 decoder:
?UTF-8 中的编码是0xC3A4，所以这就是 MySQL 使用的。utf-8 解码器证实了这一点：
53414D31302F32303130C3A4C3B6C3BCC39F2A23C3A92D656E
-->
SAM10/2010??ü?*#é-en
The reason it is cut off is your MySQL client, which indicates the cut off with ...at the end.
它被切断的原因是您的 MySQL 客户端，它表示...以结尾的切断。
The SQL Server encoding is Latin1_General (aka Windows-1252)
SQL Server 编码为 Latin1_General（又名 Windows-1252）
The SQL Server encoding of ?is 0xE4. It's probably encoded in SQL Server's Latin1_Generalcollation, which corresponds to Windows-1252. The other characters ?ü?convert to 0xF6FCDFunder Windows-1252, confirming the guess.
的 SQL Server 编码?是0xE4. 它可能以 SQL Server 的Latin1_General排序规则编码，对应于Windows-1252。其他字符在 Windows-1252 下?ü?转换为0xF6FCDF，确认猜测。
To force SQL Server to use a different encoding, specify the collateclause:
要强制 SQL Server 使用不同的编码，请指定collate子句：
cast('?ü?' AS varchar(5)) collate French_CS_AS
The SQL Server hex string is cut off because of your cast(... as varbinary). When n is not specified with the CAST function, the default length is 30. Try to specify the size explicitly, or set it to max:
SQL Server 十六进制字符串由于您的cast(... as varbinary). 当未使用 CAST 函数指定 n 时，默认长度为 30。尝试明确指定大小，或将其设置为max：
cast('abcd' as varbinary(max))
^^^^^
回答by Sonam
This query may solve the purpose:
这个查询可以解决这个目的：
Declare @b varbinary(max)
Select @b=Cast(CONVERT(varbinary(4), '0x' + @HexValue, 1) As varbinary)
