問(wèn)題描述

我從表(源)中執(zhí)行 INSERT SELECT，其中每一列都是 VARCHAR 數(shù)據(jù)類型.

I do an INSERT SELECT from a table (source) where every column is of VARCHAR datatype.

其中一列存儲(chǔ)二進(jìn)制數(shù)據(jù)，如

One of the columns stores binary data like

'0003f80075177fe6'

我插入的目標(biāo)表具有相同的列，但具有正確的 BINARY(16) 數(shù)據(jù)類型.

The destination table, where I insert this, has the same column, but with proper data type of BINARY(16).

INSERT INTO destination
(
    column1,        --type of BINARY(16)
    ...
)
SELECT
    CONVERT(BINARY(16),[varchar_column_storing_binary_data]),   --'0003f80075177fe6'
FROM source
GO

當(dāng)我插入它，然后選擇目標(biāo)表時(shí)，我從 BINARY16 列中得到了一個(gè)不同的值:

When I insert it, then select the destination table, I got a different value from the BINARY16 column:

0x30303033663830303735313737666536

這看起來(lái)不像是同一個(gè)值.

It does not really seems like the same value.

將存儲(chǔ)為 VARCHAR 的二進(jìn)制數(shù)據(jù)轉(zhuǎn)換為 BINARY 列的正確方法是什么?

What should be the proper way to convert binary data stored as VARCHAR to BINARY column?

推薦答案

你得到的結(jié)果是因?yàn)樽址?003f80075177fe6"(一個(gè) VARCHAR 值)被轉(zhuǎn)換為代碼點(diǎn)，而這些代碼點(diǎn)作為二進(jìn)制值提供.由于您可能正在使用與 ASCII 兼容的排序規(guī)則，這意味著您將獲得 ASCII 代碼點(diǎn):0 是 48(30 進(jìn)制)，f 是 102(66 進(jìn)制)等等.這解釋了 30 30 30 33 66 38 30 30...

The result you get is because the string "0003f80075177fe6" (a VARCHAR value) is converted to code points, and these code points are served up as a binary value. Since you're probably using an ASCII-compatible collation, that means you get the ASCII code points: 0 is 48 (30 hex), f is 102 (66 hex) and so on. This explains the 30 30 30 33 66 38 30 30...

您想要做的是將字符串解析為字節(jié)的十六進(jìn)制表示 (00 03 f8 00 75 71 77 fe 66).CONVERT 接受額外的樣式"允許您轉(zhuǎn)換十六進(jìn)制字符串的參數(shù):

What you want to do instead is parse the string as a hexadecimal representation of the bytes (00 03 f8 00 75 71 77 fe 66). CONVERT accepts an extra "style" parameter that allows you to convert hexstrings:

SELECT CONVERT(BINARY(16), '0003f80075177fe6', 2)

樣式 2 將十六進(jìn)制字符串轉(zhuǎn)換為二進(jìn)制字符串.(樣式 1 對(duì)以0x"開(kāi)頭的字符串執(zhí)行相同的操作，但此處并非如此.)

Style 2 converts a hexstring to binary. (Style 1 does the same for strings that start with "0x", which is not the case here.)

請(qǐng)注意，如果少于 16 個(gè)字節(jié)(如本例中所示)，則該值右填充零(0x0003F80075177FE60000000000000000).如果您需要左填充，則必須自己執(zhí)行此操作:

Note that if there are less than 16 bytes (as in this case), the value is right-padded with zeroes (0x0003F80075177FE60000000000000000). If you need it left-padded instead, you have to do that yourself:

SELECT CONVERT(BINARY(16), RIGHT(REPLICATE('00', 16) + '0003f80075177fe6', 32), 2)

最后，請(qǐng)注意，二進(jìn)制文字可以在不進(jìn)行轉(zhuǎn)換的情況下簡(jiǎn)單地通過(guò)在它們前面加上0x"而不使用引號(hào)來(lái)指定:SELECT 0x0003f80075177fe6 將返回類型為 BINARY(8).與此查詢無(wú)關(guān)，只是為了完整性.