Best Datatype To Store A Long Number Made Of 0 And 1

I want to know what's the best datatype to store these: null 0 /* the length of other numbers is always 7 digits */ 0000000 0000001 0000010 0000011 /* and so on */ 1111111 I have

Solution 1:

What you are showing are binary numbers

• 0000000 = 0
• 0000001 = 2^0 = 1
• 0000010 = 2^1 = 2
• 0000011 = 2^0 + 2^1 = 3

So simply store these numbers in an integer data type (which is internally stored with bits as shown of course). You could use BIGINT for this, as recommended in the docs for bitwise operations (http://dev.mysql.com/doc/refman/5.7/en/bit-functions.html).

Here is how to set flag n:

UPDATE mytable
SET bitmask = POW(2, n-1)
WHERE id = 12345;

Here is how to add a flag:

UPDATE mytable
SET bitmask = bitmask | POW(2, n-1)
WHERE id = 12345;

Here is how to check a flag:

SELECT *
FROM mytable
WHERE bitmask & POW(2, n-1)

But as mentioned in the comments: In a relational database you usually use columns and tables to show attributes and relations rather than an encoded flag list.

Solution 2:

As you've said in a comment, the values 01 and 1 should not be treated as equivalent (which rules out binary where they would be), so you could just store as a string.

It actually might be more efficient than storing as a byte + offset since that would take up 9 characters, whereas you need a maximum of 7 characters

Simply store as a varchar(7) or whatever the equivalent is in MySql. No need to be clever about it, especially since you are interested in extracting positional values.

Don't forget to bear in mind that this takes up a lot more storage than storing as a bit(7), since you are essentially storing 7 bytes (or whatever the storage unit is for each level of precision in a varchar), not 7 bits.

If that's not an issue then no need to over-engineer it.

Solution 3:

You could convert the binary number to a string, with an additional byte to specify the number of leading zeros.

Example - the representation of 010:

• The numeric value in hex is 0x02.
• There is one leading zero, so the first byte is 0x01.
• The result string is 0x01,0x02.

With the same method, 1010010 should be represented as 0x00,0x52.

Seems to me pretty efficient.

Solution 4:

Not sure if it is the best datatype, but you may want to try BIT: MySQL, PostgreSQL

There are also some useful bit functions in MySQL.