Binary data in MySQL

Question

I need to store binary data, such as 1110000, in MySQL. When I select it, I need the return value to be the same 1110000 again.

What data type should I use? Can I use bit? Or would varbinary be better?

Solution 1

When you're dealing with binary numbers you can use a bit field, e.g.:

bit(64)

is a bit field with up to 64 significant bits (the maximum size allowed).

In order to insert constant values, you can use the b'value' notation like so:

insert into bits values (b'0001001101001');

You can convert a bit field to a number by just adding 0 or using cast(). There's also the handy bin(), hex(), and oct() function to print the value in a particular base.

If non-numeric, varbinary or blob would be the most efficient storage method, binary is also available (it will pad shorter values with nil bytes tho).

In case, you don't want to deal with the conversions, you can store the string in a varchar or char. It will only use up about 8 times the space of a compact varbinary.

To insert/read from your app, you'll need to convert your sequence into a packed byte array, then store the packed string in the varbinary column. In C# you might use BitConverter, for php you might use pack/unpack.

Solution 2

Hope, this code can help you:

---

for select:

     select conv(column_name, from_base, to_base) from table_name
     //example:
     select conv(column1, 10, 2) from table1;

for insert:

     insert into table_name(column1) values( B'binary_data') ;
     //example : 
     insert into table1(column1) values( B'1110000');

for query:

    select column1,column2 from table_name where column1 & B('binary_data');
    //example: 
    select column1, column2 from table1 where column1 & B('1110000');