CODEWITHSUNDEEP
mysqlsqlsqldatatypes
stack
stack

Reputation: 10228

Best datatype to store a long number made of 0 and 1

I want to know what's the best datatype to store these:

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?

Upvotes: 3

Views: 891

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

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.

Upvotes: 2

Charleh
Charleh

Reputation: 14002

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.

Upvotes: 2

Gary Sham
Gary Sham

Reputation: 503

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.

Upvotes: 1

kmaork
kmaork

Reputation: 6022

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.

Upvotes: 1

Related Questions