Ampersand (&) operator in MySql WHERE Clause-CodePudding

What this passage does? (xyz & 192 > 0) in

SELECT * FROM table WHERE (xyz & 192 > 0)

192 is created from two values 64 128

xyz can be: 1,2,4,8,16,32,64,128 ... or the sum of some of these values, for example 64 128

CodePudding user response：

It's the bitwise AND operator:

https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html#operator_bitwise-and

which performs AND on each pair of bits in the two arguments:

https://en.wikipedia.org/wiki/Bitwise_operation#AND

and has higher precedence than >

https://dev.mysql.com/doc/refman/8.0/en/operator-precedence.html

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-,  
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

CodePudding user response：

(xyz & 192 > 0) checks if the sum xyz has the elements either 64 and/or 128 as its values

CodePudding user response：

This is not intended to be an answer.
The answers of Chris & Tom combined explain it.
But I was wondering how the bitwise-AND operator resulted for different numbers.

with recursive NUMS (num) as (
  select 1 as num
  union all
  select num 1
  from NUMS
  where num < 575
)
select min(num) minNum, max(num) maxNum
, (num & 192) as chk
from NUMS
group by chk, cast((num - MOD(num, 64))/64 as unsigned)

minNum | maxNum | chk
-----: | -----: | --:
     1 |     63 |   0
    64 |    127 |  64
   128 |    191 | 128
   192 |    255 | 192
   256 |    319 |   0
   320 |    383 |  64
   384 |    447 | 128
   448 |    511 | 192
   512 |    575 |   0

db<>fiddle here

So okay, (xyz & 192 > 0) could be used to verify if xyz is between 64 and 255.