Store 8 feature flags in 1 integer (Bitwise operations in MySQL)

This video is a tutorial on using bits & bitmasks for storing feature flags in a database. It involves compressing all data into a single integer column and using bitmasks to activate specific features.

While this method is technically interesting and efficient for compact data storage, it can be convoluted to understand there's no way to properly index it.

In future videos, alternative techniques such as storing the flags in a JSON column or a separate table using a many-to-many relationship will be discussed.

------
00:00 Feature flags
00:57 Bits and bytes
02:42 Bitwise operators
03:58 Querying with bitmasks
09:06 Adding and removing flags
13:17 Finding unused flags and indexing
14:54 Pros and cons
------

💬 Follow PlanetScale on social media

[bravethomasyt]

This is equally beautiful and horrifying.

[Wizatek]

It gets a lot easier to use when you also use bit shifting. WHERE flag & (1 << 0) for the first bit, or WHERE flag & (1 << 10) for the 11th bit

[igrb]

I'm at awe with how good the explanations are, the editing helps so much and the delivery is just on point. Didn't think the yt channel for a database company would be so good but am pleasantly surprised!

[henryroberts]

Very good explanation of Bitwise. I will most likely need to watch again at a later times. Thanks for the video.

[arasyyoram]

bitwise is great, i've learned it from how discord handle permission when i developed a bot. love the explanation on this video

[RaicaBogdan]

Steve needs a bonus for this video! Good information, interesting video... that's all I can say.

[KsaR-]

I've using this idea for several years. For readability in code, you can use either the binded parameters or enums/classess instead of integers on programming side.

[LiveNobin]

My relationship with MySQL more than 20 years but this video explains me how Bitwise operations work. Thank you for this nice explanation. I understand that this is not useful for me to use in any production application :)

[ahmad-murery]

While it looks unreadable, inefficient and not wise to use it in modern applications, well, this was a complete wisdom in the 8bit era where you need to make use of every bit to save memory (real tough programmer back then were able to to do logical operation really quick).

Now, regardless of the size it does take, isn't column of type SET is a good alternative to this, I read it can hold up to 64 different string values.

Thanks Aaron!

[spaceemotion]

I remember using this system when developing a tiny packet system that needed to transfer quite a lot of flags. i think not using them inside of SQL is a lot more intuitive when dealing with things on a constrained hardware level (e.g. playing around with networked Arduino stuff)

[seccentral]

I did something similar for a local isp when I was learning linux by automating iptables and traffic control with bash cron scripts to handle assigning and changing each subscriber's access tier and allocated bandwidth bucket and other network privilege flags encoded like this.
That system worked flawlessly for years and logged everything - also encoded 😂to save on space. I fully understand why you say "please don't use this if it's supposed to interact with humans". Luckily there was a web frontend available that had the logic to decode this in human language.
Great vid 🥰🥰

[LenWoodward]

I love bit masks (when managed by my project and not the DB obv). In PHP I set up an integer backed enum, and set each case as `0x1` while bitshifting left using the `<< 4`, `<< 6`, etc operator for readability

[macwilko]

PlanetSclae absolutely rocks, these videos are incredibly helpful!

[nelsone]

*This **_flags_** thing is amazing!* I constantly use it in my applications; I had never used it in SQL.

But, to update it adding/removing flags in the application layer, obviously, you have to fetch the current value from the database first (which can be outdated when you push the new value without locking the row). I never thought about using bitwise in SQL queries to update the values. 🤯

[emilenevoldsen]

Reminded me about when I reprogrammed my Audi: +2 ambient mirror, +4 mirror turn signals, +8 interior door handle, +16 automatic run etc. Was painfull to figure out what was on/off in the early 2000 with just a regular calculator 😂

[abdellahcodes]

knew all the bitwise magic beforehand,

but I watched till the end for entertainment :)

[pedrojuglar]

I learned about bitwise operations and how to use them for config flags when I was setting up multiplayer games in Quake 2 around 25 years ago. Turning on and off settings was updating a number on screen which was then saved in a config file. I'm proud of the fact that I figured out how it works on my own. I was 15 or so at that time.

[FrankJonen]

That made me chuckle a bit. You basically created dip switches. Not that unhinged. I might actually use this.

[jesusrdev]

Thanks for the great SQL videos! They've been really helpful, especially as I'm designing a complex database schema for a multi-tenant SaaS app.

I appreciate the way you break down complex concepts and explain the "why" behind things.

Keep up the great work!

[bionoid]

Been using bitwise forever and still do in DB; You're not limited to just 8 values, you can go up to at least 31 bits before you need to worry about sign. And especially if you're passing this value to a Javascript frontend they are restricted to 32bit integers regardless of architecture so watch out for this.

A suggestion I would throw in is to keep track of the bit offset values in the flags comment field. Eg. 1:Admin, 2:User, 32:Disabled, 64:Deleted