MySQL data type for 128 bit integers

Question

I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers.

Right now, I'm using binary(16) but that involves a lot of conversion function pack(/huge number in hex .../).

Is there a best datatype to store a 128 bit unsigned integer?

Answer 1

I don't know what the best way necessarily is to store it -- but there's at least a better option than using a varchar(39) (or varchar(40) if you needed it signed) ; instead use a decimal(39,0). From the mysql docs:

Fixed-Point (Exact-Value) Types

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

MySQL 5.1 stores DECIMAL values in binary format. Before MySQL 5.0.3, they were stored as strings. See Section 11.18, “Precision Math”.

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), where the implementation is permitted to decide the value of M. MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10.

If the scale is 0, DECIMAL values contain no decimal point or fractional part.

The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are permitted by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the permissible number of digits.)

It's stored packed, so it'll take up less space than the varchar (18 bytes, if I'm doing my math right), and I'd hope you'd be able to do math on it directly, but I've never tried with that large of a number to see what happens.

Answer 2

I found myself asking this question and from all the posts I read never found any performance comparisons. So here's my attempt.

I've created the following tables, populated with 2,000,000 random ip address from 100 random networks.

CREATE TABLE ipv6_address_binary (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    addr BINARY(16) NOT NULL UNIQUE
);

CREATE TABLE ipv6_address_twobigints (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    haddr BIGINT UNSIGNED NOT NULL,
    laddr BIGINT UNSIGNED NOT NULL,
    UNIQUE uidx (haddr, laddr)
);

CREATE TABLE ipv6_address_decimal (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    addr DECIMAL(39,0) NOT NULL UNIQUE
);

Then I SELECT all ip addresses for each network and record the response time. Average response time on the twobigints table is about 1 second while on the binary table it is about one-hundredth of a second.

Here are the queries.

Note:

X_[HIGH/LOW] is the most/least significant 64-bits of X

when NETMASK_LOW is 0 the AND condition is omitted as it always yields true. doesn't affect performance very much.

SELECT COUNT(*) FROM ipv6_address_twobigints
WHERE haddr & NETMASK_HIGH = NETWORK_HIGH
AND laddr & NETMASK_LOW = NETWORK_LOW

SELECT COUNT(*) FROM ipv6_address_binary
WHERE addr >= NETWORK
AND addr <= BROADCAST

SELECT COUNT(*) FROM ipv6_address_decimal
WHERE addr >= NETWORK
AND addr <= BROADCAST

Average response times:

BINARY_InnoDB  0.0119529819489
BINARY_MyISAM  0.0139244818687
DECIMAL_InnoDB 0.017379629612
DECIMAL_MyISAM 0.0179929423332
BIGINT_InnoDB  0.782350552082
BIGINT_MyISAM  1.07809265852

Answer 3

I believe the only other option is to store it in a varchar(39) field.

Answer 4

I use a base64 string that uses 22bytes (4 byte overhead). The reason I do this, is just because every platform supports base64 and strings and is easy to work with.

I found no real performance gain between 22bytes and 18bytes on MySql and Oracle. I did however found significant improvement when using two 64bit unsigned integers, but it's a pain to work with and unless performance is extremely critical I'd suggest just go with a single column and 22bytes.