Store 128 bit binary number in MYSQL binary data type

Question

In my research I've looked at MySql insert statement to binary datatype? and https://dba.stackexchange.com/questions/904/mysql-data-type-for-128-bit-integers

But I can't seem to replicate their results.

I am converting the 64 squares of a chess board into a 64 bit binary number

Like so 000010010100010110100011011001000100011110001010001000100000110

I have a column in the mysql table of type binary(8)

My assumption being it needs to be of size 8 bytes because it is 64 bits, however it is claiming the input I am trying to provide is too large.

I can find very little resources on the binary data type only and I know I must be making some mistake, I assume it is trying to read each value as a char to something but I am not sure why.

Please assist me on how I may store this large number in my table.

A "binary" data type like BLOB does not represent a number, just binary data. They are typically used to store images, MP3 audio, video, etc. — The Impaler
– The Impaler, Commented Mar 16, 2021 at 0:40
Ah I knew I must have been mis-understanding what type it was, so if I need to store two 64 bit numbers should I just split it up into two Big Ints? How is the search speed on a Big Int? — Marc-9
– Marc-9, Commented Mar 16, 2021 at 1:54
It depends on how you would like to search and/or update it. Maybe a UNSIGNED BIGINT or a CHAR(64) could be easier. — The Impaler
– The Impaler, Commented Mar 16, 2021 at 1:58
Here is a link Where I explain what I planned to do, I am currently filling a dummy db with info and running some tests to see what data types get me the best search speeds and minimize space — Marc-9
– Marc-9, Commented Mar 16, 2021 at 2:25

Vatev · Accepted Answer · 2021-03-16 12:50:26Z

1

MySQL interprets the number literal 000010010100010110100011011001000100011110001010001000100000110 as a decimal number, which is larger than 8 bytes.

The binary data types work similarly to char/varchar/text, but the values can be any sequence of bytes.

You can use string literals with proper escaping, but it's difficult to read those manually.

The most convenient and universal way (that I know of) is to use the HEX and UNHEX functions to convert between hexadecimal representation and the bytes in the actual data. E.g:

INSERT INTO tbl (col) VALUES (UNHEX('5BA238AEB1E956BDEA214C4C7FE9951B'))


SELECT HEX(col) FROM tbl

You can also use binary number literals:

INSERT INTO tbl (col) VALUES (0b000010010100010110100011011001000100011110001010001000100000110)

If the values are 64 bit, you can use a BIGINT field.

answered Mar 16, 2021 at 12:50

Vatev

7,5801 gold badge36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Store 128 bit binary number in MYSQL binary data type

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related