In MySql/C# how to convert a 128 bit integer to a varbinary(16) in a fast manner

Question

I'm far from being a MySql expert, in fact I have never written 'a stored procedure', which might be what I need here.

What I have: A (up to ) 128 bit integer - as a string (it's actually an Ipv6 address).

What I want : A MySql varbinary(16) value.

I'm trying to convert a 128 bit integer, so it can be stored in MySql as varbinary(16).

From what I know, the only type in MySql, that can handle such a big integer is the decimal data type. However there seems to be no conversions from decimal to either hex string or varbinary. I want to use varbinary(16) for 2 reasons:

It's the most compact.
Both MySql and C# can directly create Ipv6 addresses from this format. And the opposite is easy too.

I have tried several (miserable) attempts with 'CAST', 'HEX' and ip conversion functions. They don't seem to work with Decimal.

However, I managed to create this query in C#, that works, but is slow:

string value1 = "281470698520576";  // example1 (less than 128 bit - must be leftpadded with zeros).
string value2 = "42541870534966271977089220242718064640";  // example2 (128 bit)

StringBuilder sb = new StringBuilder("INSERT INTO ......... VALUES");
sb.Append("(").Append("UNHEX('").Append(BigInteger.Parse(value1).ToString("x32")).Append("'), ")
  .Append("UNHEX('").Append(BigInteger.Parse(value2 ).ToString("x32")).Append("')");

I would prefer to 'do it in MySql' since that's normally faster, but I can use any combination of C#/MySql - may be a stored procedure.

Is there a way to do it in MySql?

Have you considered the builtin [INET6_ATON() function](https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_inet6-aton)? — Bill Karwin, Apr 27 '21 at 00:27
@BillKarwin: Yes, it doesn't work with `decimal`, only with standard hex ipv6 format. — Poul Bak, Apr 27 '21 at 00:28
Well, if you started with IPv6 format, like `abcd:1234:fde::1:2`, skip the step of building the integer. — Rick James, Apr 27 '21 at 04:25
@RickJames: I start with a 128 bit integer (as a string)! That's why I need this. — Poul Bak, Apr 27 '21 at 12:55
Depending on the number of rows, you may need to adjust how you perform string concatenations. https://stackoverflow.com/questions/21078/most-efficient-way-to-concatenate-strings — Vlam, Apr 28 '21 at 03:00
You can also try setting the initial capacity for the StringBuilder during initialization if you know the number of rows to insert. That will speed things up. — Vlam, Apr 28 '21 at 03:06
@Vlam: In real code I already create the `StringBuilder` with capacity : 0x40000. and the values part is looped. Guess I should have mentioned that. — Poul Bak, Apr 30 '21 at 03:26

Rick James · Answer 1 · 2021-04-28T00:51:09.860

Doing in MySQL will be tedious. Here's an outline of what might go into a Stored Function.

Stuff them in a DECIMAL(40,0) column (assuming that is big enough).

Do div and mod 2^16 to get the 8 chunks; convert each to hex (CONV('12345', 10, 16)).

Then do one of the following 2 things:

Plan A:

CONCAT_WS(':', ...) to combine the pieces. That will be valid IPv6 syntax, but not the minimal. (Don't need to worry about missing leading zeros.)

INET6_ATON('...') will generate 0x...

INSERT INTO ... VALUES ( ..., 0xFDFE0000000000005A55CAFFFEFA9089 ,... ) to stuff it into a BINARY(16) column.

Plan B:

CONCAT the pieces.

Make sure to have leading zeros on each chunk: RIGHT(CONCAT('000', chunk), 4)

INSERT INTO ... VALUES ( ..., UNHEX('FDFE0000000000005A55CAFFFEFA9089') ,... ) to stuff it into a BINARY(16) column.

Poul Bak · Accepted Answer · 2021-05-02T20:14:28.080

In MySql I created a stored function.

Here it is - for future readers:

DELIMITER $$
CREATE DEFINER=`mydb`@`%` FUNCTION `Int128ToVarBinary`(`Int128` DECIMAL(60,20) UNSIGNED) RETURNS varbinary(16)
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Converts 128 bit (DECIMAL) Ipv6 address to VARBINARY(16)'
BEGIN

DECLARE TwoExp64 DECIMAL(20) UNSIGNED;
DECLARE HighPart BIGINT UNSIGNED;
DECLARE LowPart BIGINT UNSIGNED;

SET TwoExp64 = 18446744073709551616;
SET HighPart = Int128 DIV TwoExp64;
SET LowPart = Int128 MOD TwoExp64;

RETURN UNHEX(CONCAT(LPAD(HEX(HighPart), 16, '0'), LPAD(HEX(LowPart), 16, '0')));

END$$
DELIMITER ;

Explanation:

The function takes an integer (up to 128 bit), here containing the decimal representation of an Ipv6 address, as its parameter and returns a varbinary(16) - 16 bytes long binary string.

I start by declaring TwoExp64 as 2^64. I then DIV and MOD Int128 with this value to get 2 BIGINT values so I can HEX/UNHEX them to get the binary string/byte array (left padded to a length of 16).

How I use it in C# building the query:

StringBuilder sb = new StringBuilder("INSERT INTO ......... VALUES", 0x80000);
bool dataOk = false;

// and then in a loop coming from a csv file:
string value1 = "281470698520576";  // example1 (less than 128 bit - must be leftpadded with zeros).
string value2 = "42541870534966271977089220242718064640";  // example2 (128 bit)

sb.Append((dataOk) ? ", " : "").Append("(").Append("Int128ToVarBinary(").Append(value1).Append("), ").Append("Int128ToVarBinary(").Append(value2).Append("))");                                         dataOk = true;

The differences from @Rick James's answer are:

It's important to use `DECIMAL(60,20) for the parameter type, otherwise the divisions will not be correct.
I only do one DIV/MOD operation with the decimal type. That gives me 2 BIGINT values. That speeds things up.

BTW: When testing, I did the classical mistake: Tested on my development computer, which is not busy and has plenty of cores to run in parallel. That test showed that the C# version using BigInteger was the fastest. However when I finally tested on the production server, which is busy, the MySql stored function shown above was fastest.

So, remember to speed test on production server too :)

In MySql/C# how to convert a 128 bit integer to a varbinary(16) in a fast manner

2 Answers2