在 MySQL 中使用二进制数据 [英] Working with Binary Data in MySQL

问题描述

SELECT 
      0x0000987C As col1,
      substr(BinaryData,1,4) As col2,
      CAST(0x0000987C  AS SIGNED) As col3,
      CAST(substr(BinaryData,1,4)  AS SIGNED) As col4
FROM
(
SELECT 0x0000987C00000000 AS BinaryData
) d

退货

col1  col2   col3  col4
----  ----  -----  ----
BLOB  BLOB  39036   0

当我查看 col1 和 col2 的 BLOB 查看器时，它们看起来完全相同(下面的屏幕截图).

When I look at the BLOB viewer for col1 and col2 they both appear identical (screenshot below).

那么为什么 col3 和 col4 的结果不同呢?

So why the different results for col3 and col4?

推荐答案

我认为这与数据类型有关.BinaryData 有一个整数数据类型，但 substr(BinaryData,1,4) 需要一个字符串.CAST 然后对结果感到困惑.此外，CAST 使用基数 10 解析字符串，因此您需要做一些额外的工作.试试这个:

I think it has to do with data types. BinaryData has an integer data type, but substr(BinaryData,1,4) expects a string. CAST then gets confused with the result. Also, CAST parses strings using base 10, so you need to a little bit of extra work. Try this:

CAST(CONV(substr(HEX(BinaryData),1,8), 16, 10)  AS SIGNED) As col4

这是一个怪物，但它应该给你你想要的.

It's a monster, but it should give you what you want.

这篇关于在 MySQL 中使用二进制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！