MySQL SELECT strings where a specific integer bit is specified

Question

I need to make a selection request in the posting table where a specific integer bit is given. An integer is a set of categories in a bitmask: For example.

1 => health 2 => marketing 3 => personal 4 => music 5 => video 6 => design 7 => fashion 8 => ...... 

Sample data:

 id | categories | title 1 | 11 | bla bla 2 | 48 | blabla, too 

I need a mysql query that selects transactions marked with a specific category. Say, "all videos" This means that I need a result set of messages where the 5th bit of the catgories column is set (for example, 16,17,48 ....)

 SELECT * FROM postings WHERE ....???? 

Any ideas?

Answer 1

You can use bitwise operators like this. For video (bit 5):

 WHERE categories & 16 = 16 

Substitute 16 using the following values ​​for each bit:

 1 = 1 2 = 2 3 = 4 4 = 8 5 = 16 6 = 32 7 = 64 8 = 128 

This comes from the least significant bit to the highest, which is the opposite of what most programmers think. They also start from scratch.

Answer 2

What about

 SELECT * FROM postings WHERE (categories & 16) > 0; -- 16 is 5th bit over 

One problem is that you probably won’t get into the index, so you may run into primary problems if this is a large amount of data.

Some databases (such as PostgreSQL) allow you to define an index for such an expression. I am not sure if mySQL has this function. If this is important, you can consider splitting them into separate Boolean columns or a new table.

Answer 3

SQL (and not just mySQL) is not suitable for bitwise operations. If you are beaten And you will force a table scan, because SQL will not be able to use any index and will check each row one at a time.

It would be better if you created a separate “Categories” table and a properly indexed Many-to-Many PostingCategories table to connect the two.

UPDATE

For people who insist that bitmap fields are not a problem, this helps to check the Joe Celko BIT problem . The bottom of this article lists serious problems caused by bitmap images.

Regarding the comment that the empty statement cannot be right, mark # 10 - it splits 1NF, so yes, the bitmap fields are bad:

• Data is not readable ....
• Limitations are b #### for the record ....
• You are limited to two values ​​for each field. This is very restrictive; even an ISO sex code cannot fit into such a column ...
• A temporary element for the bitmask (or for individual bit flags) is missing. For example, the flag "is_legal_adult_flg" ... DATE for the date of birth (only 3 bytes) will have a complete fact and let us know what we need to know; that would always be right ....
• You will learn that using flags will tend to split the state of an object into multiple tables ....
• Bit flags offer redundancy. On the system I just mentioned, we had "is_active_flg" and "is_completed_flg" in the same table. A completed auction is not active and vice versa. This is the same fact in two flags. Human psychology (and English) prefers to hear the affirmative wording (remember the old song "Yes, today we have no bananas!"?). All of these bit flags and sequence validation are replaced by two sets of state transition tables, one for rates and one for deliveries. Details of transition state restrictions. The history of each auction is now in one place and must comply with business rules.
• By the time you parse a column with a bitmask and throw out fields, you do not need performance, simpler data types will not be improved.
• Grouping and sorting by individual fields is a real pain. Give it a try.
• You need to index the entire column, so if you are not lucky and put them in the correct order, you are stuck in scanning tables.
• Since the bitmask is not in its first normal form (1NF), you have all the anomalies that we wanted to avoid in the DBMS.

I would also add, what about NULL? What about the missing flags? What if something is neither true nor false?

Finally, with regard to the compression requirement, most byte packets fill the bit fields in bytes and ints internally. In this case, the bitmap field does not offer any compression. Other databases (e.g. PostgreSQL) actually have a Boolean type, which can be true / false / unknown. This may take 1 byte, but not much storage and transparent compression is available if the table gets too large.

In fact, if the table becomes large, problems with bit fields become much more serious. Saving a few MB in a GB table is not an improvement if you are forced to use table scanning or if you lose the ability to group