It is time to JSON! ...most likely

[Yehonal]

Hello there! I recently had a discussion here #8377 (review) about these flags that are saved in our DB and that, honestly, in my opinion, sometimes are getting a little bit out of control.

While null values in MySQL do not consume any space (just a very small amount as stated here: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html ) ...

While a NULL itself does not require any storage space, NDB reserves 4 bytes per row if the table definition contains any columns allowing NULL, up to 32 NULL columns. (If an NDB Cluster table is defined with more than 32 NULL columns up to 64 NULL columns, then 8 bytes per row are reserved.)

...having dozens of fields to save every single flag/status is a bit weird.

However, since version 5.7 the new JSON fields have been introduced in MySQL, which are not only a way to convert our bitmask values into something which is better readable, but the JSON properties are also indexable and can be converted to generated columns that do not consume any further space, except the one for the index itself: https://www.compose.com/articles/mysql-for-json-generated-columns-and-indexing/

Moreover, we do not need any library to parse the JSON data from the DB, but we can use a syntax just like this:

SELECT 
attributes->>"$.attr1" AS attr1
attributes->>"$.attr2" AS attr2
FROM spell_custom_attributes
WHERE entry = ...;

And the same to update some properties:

UPDATE spell_custom_attributes SET attributes = 
JSON_MERGE_PATCH(`attributes`, '{"attr1": true, "attr2": "I am attr2"}')
WHERE entry = ...;

This example is not the best one, but it should give the idea

This is similar to the concept of a NoSQL database inside MySQL and I successfully used this strategy for several of my Projects. Although I still think that bitmasks fields can and should be used for pure boolean flags, we can consider the JSON alternative when we want to store more dynamic information (e.g. inside the character database)

What do you think?

[Kaev]

Looks good to me, more readability is always a plus if it doesn't cause any performance issues.
Maybe we can rename all the flag columns during that so we don't have 5 different naming conventions anymore. (I'm not getting tired mentioning that fact :P)