Here's a neat trick that was shown to me by someone who had a lot more experience with MySQL. You may already know and use this, but since it's not obvious, maybe some other people like me have missed it.
Basically, my problem was that I had a need not just to read/write bit vectors, but also to sometimes tweak them manually (as in when adding new spells), refer to them in queries (as in, show me all players flagged as self-deleted), and modify them easily via our new web-based building tool (make such and such mob a sentinel).
There are ways to do all that even if you store bit vectors as INT, but they're not easy to just roll out off the top of your head. That's where the 'set' data type comes in. You simply define the bit vector column as, e. g. set('magical', 'non-metal','artifact'), listing keywords for each of the flags in the same order they're defined in your code.
And that's it. You can then query for all items like '%magical%', and you'll see the actual flag values listed in select statement results. To toggle a bit off / on, you simply list or not list it, e. g.
update objects set item_flags=('magical','artifact');
Retrieving the value as an INT is also easy:
'select `item_flags`+0 from objects'
I'm not sure if other SQL frameworks have something similar–maybe folks who know will chime in.
Documentation of the MySQL 'set' data type is found here.
Basically, my problem was that I had a need not just to read/write bit vectors, but also to sometimes tweak them manually (as in when adding new spells), refer to them in queries (as in, show me all players flagged as self-deleted), and modify them easily via our new web-based building tool (make such and such mob a sentinel).
There are ways to do all that even if you store bit vectors as INT, but they're not easy to just roll out off the top of your head. That's where the 'set' data type comes in. You simply define the bit vector column as, e. g. set('magical', 'non-metal','artifact'), listing keywords for each of the flags in the same order they're defined in your code.
And that's it. You can then query for all items like '%magical%', and you'll see the actual flag values listed in select statement results. To toggle a bit off / on, you simply list or not list it, e. g.
update objects set item_flags=('magical','artifact');Retrieving the value as an INT is also easy:
I'm not sure if other SQL frameworks have something similar–maybe folks who know will chime in.
Documentation of the MySQL 'set' data type is found here.