stack stack - 7 months ago 8
SQL Question

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 tested,
INT
works as well. But there is a better datatype. Because all my numbers are made of
0
or
1
digits. Is there any better datatype?

Answer

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.