How to use bitwise MySQL operations in php?

Question

im trying to use bitwise mysql operations for my query and i have this example:

table1 id ptid 1 3 2 20 3 66 4 6 table2 id types 1 music 2 art 4 pictures 8 video 16 art2 32 actor 64 movies 128 .. ... 

now id = 3 from table1 is '66', because it means that it has 64 or movies and 2 or art

but

he also does not have 32 or actor twice and 2 or art ??

hope you see where my confusion is. How can I control what result I want to return. In this case, I want 64 or movies and 2 or art .

But sometimes I want three id's from table2 to belong id from table1

any ideas?

thanks

Answer 1

Using bitwise OR

The following query returns all the elements from table 2 to 66 :

 SELECT * FROM table2 WHERE id | 66 = 66 

But 32 + 32 = 64?

Although 32 + 32 = 64, this does not affect us.

Here are 64 in binary format:

 01000000 

Here are 32 in binary format:

 00100000 

Here are 2 in binary format:

 00000010 

This is position 1, which we use in this case, and not the value. There will be nothing. Each flag is on or off.

Here are 66 in binary format. Note that 64 and 2 are included, not 32:

 01000010 

Using bitwise AND instead of OR

Another way to write a request is bitwise. And like this:

 SELECT * FROM table WHERE id & 66 <> 0 

Since 0 = false for MySQL, it can be further abbreviated as follows:

 SELECT * FROM table WHERE id & 66 

Answer 2

 select * from table2 where id & 66 

Answer 3

Although the question about how to perform bitwise operations in MySQL was answered, the question in the comments about why this model cannot be optimal remains unclear.

In the above example, there are two tables; one with a bitmask and one with a breakdown of what each bit represents. It is understood that at some point the two tables must be joined together to return / display the value of the various bits.

This connection will either be explicit, for example.

 SELECT * FROM Table1 INNER JOIN TABLE2 ON table1.ptid & table2.id <> 0 

Or implicit, where you can select data from table1 into your application, and then make a second call to search for the values ​​of the bitmask, for example.

 SELECT * FROM table2 WHERE id & $id <> 0 

None of these options is an idea, because they are not "inaccessible", that is, the database cannot build a search ARGument. As a result, you cannot optimize the query using the index. The cost of the query goes beyond the impossibility of using the index, because for each row in the table, the database must calculate and evaluate the expression. This happens very quickly with the intensity of memory, processor, and I / O, and it cannot be optimized without fundamentally changing the table structure.

Besides the complete inability to optimize the query, it can also be inconvenient to read data, report data, and potentially trigger restrictions by adding more bits (64 values ​​in an 8-bit column may be accurate now, but not always so. They also make it difficult to understand systems , and I would say that this project violates the first normal form.

Although using bitmasks in a database is often a sign of poor design, there are times when you can use them. The implementation of many-to-many relationships really does not apply to those times.

A typical approach to implementing this type of relationship is something like this:

 table1 Id Val1 Val2 --------------------------- 1 ABC DEF 2 ABC DEF 3 ABC DEF 4 ABC DEF 5 ABC DEF 6 ABC DEF table2 id types ------------- 1 music 2 art 3 pictures 4 video 5 art2 6 actor 7 movies table1-table2-relationshitp table1ID Table2ID --------------------- 1 1 1 2 2 3 2 5 3 2 3 7 ... 

And you request the data this way

 SELECT table1.*, table2.types FROM table1 INNER JOIN table1-table2-relationship ON table1.id = table1-table2-relationship.table1id INNER JOIN table2 ON table1-table2-relationship.table2.id = table2.id 

Depending on the access pattern of these tables, you usually index both columns in the relationship table as a composite index (usually I consider them as a composite primary key.) This index will allow the database to quickly search for the corresponding rows in the relations table and then search for the corresponding rows in table2.

Answer 4

After playing with the answer of Marcus Adams, I thought that I would give another example that helped me understand how to join two tables using bitwise operations.

Consider the following examples of data that define a vowel table and a word table with one meaning representing the vowels present in this word.

 # Create sample tables. drop temporary table if exists Vowels; create temporary table Vowels ( Id int, Letter varchar(1) ); drop temporary table if exists Words; create temporary table Words ( Word varchar(20), Vowels int ); # Insert sample data. insert into Vowels select 1, 'a' union all select 2, 'e' union all select 4, 'i' union all select 8, 'o' union all select 16, 'u'; insert into Words select 'foo', 8 union all select 'hello', 10 union all select 'language', 19 union all select 'programming', 13 union all select 'computer', 26; 

Now we can join the Vowel table with the Word table as follows:

 # List every word with its vowels. select Word, Vowels, Letter, Id as 'Vowel Id' from ( select * from Words ) w join Vowels v where v.Id | w.Vowels = w.Vowels order by Word, Letter; 

And, of course, we can apply any conditions to the internal request.

 # List the letters for just the words with a length < 6 select Letter from ( select * from Words where length(Word) < 6 ) w join Vowels v where v.Id | w.Vowels = w.Vowels order by Word, Letter 

Answer 5

OK, this is very useful for me as well