CODEWITHSUNDEEP
mysql
Will
Will

Reputation: 75625

Dealing with 128-bit numbers in MySQL

My table has a column which I know is a 128-bit unsigned number, stored in base-10 as a varchar e.g.

"25495123833603613494099723681886"

I know the bit-fields in this number, and I want to use the top 64 bits in GROUP BY.

In my example, the top 64-bits would be 1382093432409

I have not found a way, but I have eliminated some leads:

How can I get the BIGINT that is the top 64-bits of this number, so I can use that in the GROUP BY?

Upvotes: 3

Views: 1533

Answers (1)

user149341
user149341

Reputation:

You can get at least part of the way there using floating-point math.

MySQL uses double-precision floating-point for non-integer math. This gives you about 50 bits of reliable precision for integral values - while this isn't quite 64, it's pretty close. You can use floating-point math to extract the top bits here using the expression:

FLOOR(n / POW(2,64))

where n is the name of the column.

This approach runs out of steam if more than 50 bits are needed, though, as even double-precision floats don't have enough significant bits to represent the whole thing, and trying to get any more using subtraction fails due to cancellation. (The extra bits are lost as soon as the string is converted to a number; they can't be brought back without doing something entirely different.)

Upvotes: 2

Related Questions