mysql语句解析二进制字段内容为数字

UU_Yang

已于 2024-07-09 15:37:57 修改

阅读量364

点赞数 1

分类专栏：数据库文章标签： mysql

于 2022-12-12 17:12:45 首次发布

本文链接：https://blog.csdn.net/shellching/article/details/128290039

版权

数据库专栏收录该内容

128 篇文章 2 订阅

订阅专栏

SELECT id,
CONCAT('point(',
conv(
CONCAT(mid(cast(hex(data) as char),7, 2),
mid(cast(hex(data) as char),5, 2),
mid(cast(hex(data) as char),3, 2),
mid(cast(hex(data) as char),1, 2)),
16,10)/1000000,
',',
conv(
CONCAT(mid(cast(hex(data) as char),15, 2),
mid(cast(hex(data) as char),13, 2),
mid(cast(hex(data) as char),11, 2),
mid(cast(hex(data) as char),9, 2)),16,10)/1000000,
')') AS point from m_position WHERE id = 123

结果示例：

123, point(115.149722,5.005278)

有负数经纬度的处理

if(conv( mid(cast(hex(data) as char),7, 1),16,10) < 8,
conv(CONCAT(mid(cast(hex(data) as char),7, 2), mid(cast(hex(data) as char),5, 2), mid(cast(hex(data) as char),3, 2), mid(cast(hex(data) as char),1, 2)),16,10) ,
-(-conv(CONCAT(mid(cast(hex(data) as char),7, 2), mid(cast(hex(data) as char),5, 2), mid(cast(hex(data) as char),3, 2), mid(cast(hex(data) as char),1, 2)), 16,10)&0x7fffffff)
) as lon,

if(conv( mid(cast(hex(data) as char),15, 1),16,10) < 8,
conv(CONCAT(mid(cast(hex(data) as char),15, 2), mid(cast(hex(data) as char),13, 2), mid(cast(hex(data) as char),11, 2), mid(cast(hex(data) as char),9, 2)),16,10),
-(-conv(CONCAT(mid(cast(hex(data) as char),15, 2), mid(cast(hex(data) as char),13, 2), mid(cast(hex(data) as char),11, 2), mid(cast(hex(data) as char),9, 2)), 16,10)&0x7fffffff)
)as lat