3 ms·

i love using 64 bit unsigned ints in mysql for 64 boolean columns. I do: alter table foo add column bar bigint(20) unsigned default 18446744073709551615 and t

i love using 64 bit unsigned ints in mysql for 64 boolean columns. I do: alter table foo add column bar bigint(20) unsigned default 18446744073709551615 and that gets you 64 111111111111111111111111111111 etc.

andrewfromx 11y ago
so much better than individual boolean columns cluttering up your user table.
- kbenson 11y ago
  Poe's law in action. I can't tell if you are serious, but if you are, there are generally database constructs to help with this (if the database doesn't just outright support condensing bit fields or a specific type for sets) in a way that doesn't make it impossible to read easily from the database. You're basically making your data unusable without the separate code you've written to parse that field, which can be really annoying in many, many instances (such as debugging!). Edit: It's worth noting that one way to use a complex structure in the database without making it too dependent on external code would be to create some stored procedures for setting, updating and viewing the structure. In this case, that would be some functions that took the field value, and extracted the individual bits into a result, or allowed updating a specific flag. That would keep how the data is represented within the purview of the database, but you're stuck with a bunch of SQL functions that approximate what your code is doing in the application, which is a pain. Thankfully, a lot of database systems acknowledged this a long time ago and provided constructs to make it easier.
Zikes 11y ago
That sounds pretty painful to query, though. If the first bit position represented if the user had confirmed their email address, wouldn't a SELECT for confirmed users look something like SELECT * FROM users WHERE (mydata & b'0000000000000000000000000000000000000000000000000000000000000001')?
- andrewfromx 11y ago
  in rails I add: [:foo_boolean1, :foo_boolean2].each_with_index do |flavor, i| power = 1 i.times do power *= 2 end define_method "has_#{flavor}" do (foo_options & power) > 0 end define_method "has_#{flavor}=" do |value| value = value == '1' if value.class == String current_value = self.send("has_#{flavor}") if (current_value && !value) || (!current_value && value) self.foo_options = foo_options ^ power end end end which lets me access each col like normal. blah.has_foo_boolean1 or blah.has_foo_boolean1=true etc.
  - 33degrees 11y ago
    You might want to take a look at https://github.com/pboling/flag_shih_tzu https://github.com/pboling/flag_shih_tzu, which does all of that and a lot more, like adding database scopes.
    - andrewfromx 11y ago
      aw, but it was so much fun writing that logic myself and really learning how bit ops work in ruby.
    - andrewfromx 11y ago
      reminds me of this http://jeremykun.com/2013/02/08/why-there-is-no-hitchhikers-guide-to-mathematics-for-programmers/ http://jeremykun.com/2013/02/08/why-there-is-no-hitchhikers-... article about doing Math cuz u love it.
- 33degrees 11y ago
  No, you can just compare against an integer: SELECT * FROM users WHERE mysql & 1
  - astrange 11y ago
    This sounds like it'd be a full table scan to me.

andrewfromx 11y ago

so much better than individual boolean columns cluttering up your user table.

kbenson 11y ago
Poe's law in action. I can't tell if you are serious, but if you are, there are generally database constructs to help with this (if the database doesn't just outright support condensing bit fields or a specific type for sets) in a way that doesn't make it impossible to read easily from the database. You're basically making your data unusable without the separate code you've written to parse that field, which can be really annoying in many, many instances (such as debugging!). Edit: It's worth noting that one way to use a complex structure in the database without making it too dependent on external code would be to create some stored procedures for setting, updating and viewing the structure. In this case, that would be some functions that took the field value, and extracted the individual bits into a result, or allowed updating a specific flag. That would keep how the data is represented within the purview of the database, but you're stuck with a bunch of SQL functions that approximate what your code is doing in the application, which is a pain. Thankfully, a lot of database systems acknowledged this a long time ago and provided constructs to make it easier.

Zikes 11y ago

That sounds pretty painful to query, though. If the first bit position represented if the user had confirmed their email address, wouldn't a SELECT for confirmed users look something like SELECT * FROM users WHERE (mydata & b'0000000000000000000000000000000000000000000000000000000000000001')?

andrewfromx 11y ago
in rails I add: [:foo_boolean1, :foo_boolean2].each_with_index do |flavor, i| power = 1 i.times do power *= 2 end define_method "has_#{flavor}" do (foo_options & power) > 0 end define_method "has_#{flavor}=" do |value| value = value == '1' if value.class == String current_value = self.send("has_#{flavor}") if (current_value && !value) || (!current_value && value) self.foo_options = foo_options ^ power end end end which lets me access each col like normal. blah.has_foo_boolean1 or blah.has_foo_boolean1=true etc.
- 33degrees 11y ago
  You might want to take a look at https://github.com/pboling/flag_shih_tzu https://github.com/pboling/flag_shih_tzu, which does all of that and a lot more, like adding database scopes.
  - andrewfromx 11y ago
    aw, but it was so much fun writing that logic myself and really learning how bit ops work in ruby.
  - andrewfromx 11y ago
    reminds me of this http://jeremykun.com/2013/02/08/why-there-is-no-hitchhikers-guide-to-mathematics-for-programmers/ http://jeremykun.com/2013/02/08/why-there-is-no-hitchhikers-... article about doing Math cuz u love it.
33degrees 11y ago
No, you can just compare against an integer: SELECT * FROM users WHERE mysql & 1
- astrange 11y ago
  This sounds like it'd be a full table scan to me.