1

I need to have bitmask in database. 64 bit is enough. Most use cases SELECT ... WHERE ... AND mask & value > 0 ... There are exist special datatype: BIT(64) for my case. But MySQL can make bit logical operations with BIGINT too.

What I understand at this moment about BIT:

Pros:

  • Better visual representation 0b10010000 better 144 (but I can make CONV(144,10,2) with integer and get same representation)

Cons:

  • Worse support of type (I had some problem in golang, and wrote own type to work with it), integer much more popular and easy to work with it
  • Integer types give very little bit less traffic
  • Integer more compatible with other databases

I thinking maybe BIT not worth it, and I can use UNSIGNED BIGINT, but don't want to make wrong decision for future of my database.

P.S. also I'm looking to use PostgreSQL, and there are only integer types. (Not going to migrate, but to use in another cases)

LynXz
  • 78
  • 8
  • It feels like you've answered your own question here, and there's not much more to add other than opinions, which makes it a poor fit for the Q&A format of this site. – IMSoP Sep 18 '20 at 10:29
  • I can think of a lot of problems with this, but no benefit. `bit(64)` is not a number, has no sort order or relations and the visual representation is a matter of the client application – Panagiotis Kanavos Sep 18 '20 at 10:30
  • In fact, using a *bitmask* is a problem to begin with as it makes querying a lot harder. Use separate fields for each flag and don't try to micro-optimize storage. PostgreSQL is probably the only database that supports boolean types – Panagiotis Kanavos Sep 18 '20 at 10:30
  • PostgreSQL [can use bitmap indexes over boolean fields in certain cases](https://stackoverflow.com/questions/42972726/postgresql-create-index-for-boolean-column) to optimize queries on large tables. With a bitmask on the other hand, any index created will be useless except in very limited cases - searching for a bit in the middle of a bitmask requires a full index scan – Panagiotis Kanavos Sep 18 '20 at 10:35
  • @IMSoP this is a relatively common design flaw. Check [BIT of a Problem](https://www.red-gate.com/simple-talk/sql/t-sql-programming/bit-of-a-problem/). PostgreSQL has boolean types which makes using flag fields even easier, so the harm from using bitmasks is even greater – Panagiotis Kanavos Sep 18 '20 at 10:35
  • @PanagiotisKanavos Let's not turn this into a discussion forum. Comments are intended for improving the question. If you have an answer (bearing in mind that the question is _not_ primarily about Postgres), put it in the "Your Answer" box below; if you think the question should not be answered, click "vote to close", and optionally leave a short summary of why. – IMSoP Sep 18 '20 at 10:37
  • @IMSoP I didn't start any discussion. I'll have to repeat your comment to you – Panagiotis Kanavos Sep 18 '20 at 10:38
  • @PanagiotisKanavos Fair enough, I have deleted my comment which was a reply to you and not addressing the question. My point stands that your comments appear to be attempting to answer the question not clarify it, so do not belong in this space. – IMSoP Sep 18 '20 at 10:44

0 Answers0