php - MySQL Fields alternative to 'is_active', 'is_banned' flags?

Solution:

It is good practice to use statuses column. instead of single column for every status. It can be done by serializing object with user's statuses or just simply saving JSON.

Benefits:

• easy to manage

• your table gets smaller

Solution:

Bit columns are usefull because easy to use.

An other way could be a right table (I assume you are working on a user table)

user <=> user_has_right <=> right
Copy code

Tableright is supposed to store multiple rows which areactive,banned, etc ...

Basically,user_has_right has 2 foreign keys :fk_id_user andfk_id_right. If an entry exist, then the user has the right to ...

Example : Suppose you want all active users. Supposeid_right for active users is 1.

SELECT * FROM user u
INNER JOIN user_has_right uhr ON uhr.fk_id_user = u.id_user
INNER JOIN rights r ON r.id_right = uhr.fk_id_rights
WHERE r.id_right = 1;
Copy code

Solution:

MySQL does have a BIT type, but all the bit functions and operators currently requireBIGINT (64-bit) arguments and returns. If you can live with the cast to/fromBIGINT, you can use any sufficiently large (for your application) integer type and use bitmasks with bitwiseors andands to set and clear (respectively) individual bits.

Clearly, the intended semantic of each bit-position is not explicit, and so less clear than individualis_A,is_B, ... columns, but you might be able to ameliorate that somewhat with a table of set/clear bitmasks.

It depends on what you're doing, how you use the fields, etc. and (of course) on your own particular preferences, biases, etc. on where the balance lies and whether one is better than the other for a particular situation.

Also, if you Google around, and you'll find that there may be some issues and bugs withBIT in older versions (around 5.0.x) of MySQL.