Most space efficient way to store large integer from php to mysql?

Question

I'm working with integers greater up to 128 bits long. They're used to store a large set of flags and I don't need to do any weird math with them, so I can treat it like a string in php to get around the PHP_INT_MAX limit.

I think want to store these numbers in a BINARY column in mysql. The column will need between 4-16 bytes to hold the numbers.

My understanding is that the BINARY column in php is a binary string, does this mean I'll be wasting space by not using a-z as part of the character set? Should I be using a different type of column? Do I need to base_convert in php to get the full use of the character set?

How do I get my string representation of a 128 bit integer in php stored the most efficiently into a 128 bit column in php?

Also, if roughly half of the integers I'm storing with only need 4 bytes, would I be better off using a VARBINARY column?

Answer 1

Assuming you will be using the full range of 128 bits, each number equally likely, the most space-efficient you can be is storing 128/8 = 16 chars per number.

Conversion between this binary representation and a PHP string is a little problematic though. To decode a binary string into a base-16 number you can use unpack:

$numberInHex = unpack("H*", $binaryData);

If you must convert the output to or from decimal numbers you'll have to use gmp or bc.

Update: An example:

> create table binary_test ( int128 binary(16) );
> insert into binary_test set int128 = 0x11223344556677889900112233445566;
> select hex(int128) from binary_test;
+----------------------------------+
| hex(int128)                      |
+----------------------------------+
| 11223344556677889900112233445566 |
+----------------------------------+

Instead of 0x1122... you could also use unhex('1122..').