Pre-General Availability Draft: 2017-09-21

Bit functions and operators comprise
`BIT_COUNT()`

,
`BIT_AND()`

,
`BIT_OR()`

,
`BIT_XOR()`

,
`&`

,
`|`

,
`^`

,
`~`

,
`<<`

, and
`>>`

.
(The `BIT_AND()`

,
`BIT_OR()`

, and
`BIT_XOR()`

aggregate functions are
described in Section 12.18.1, “Aggregate (GROUP BY) Function Descriptions”.) Prior to MySQL
8.0, bit functions and operators required
`BIGINT`

(64-bit integer) arguments
and returned `BIGINT`

values, so they
had a maximum range of 64 bits.
Non-`BIGINT`

arguments were converted
to `BIGINT`

prior to performing the
operation and truncation could occur.

In MySQL 8.0, bit functions and operators permit
binary string type arguments
(`BINARY`

,
`VARBINARY`

, and the
`BLOB`

types) and return a value of
like type, which enables them to take arguments and produce return
values larger than 64 bits. Nonbinary string arguments are
converted to `BIGINT`

and processed
as such, as before.

An implication of this change in behavior is that bit operations on binary string arguments might produce a different result in MySQL 8.0 than in 5.7. For information about how to prepare in MySQL 5.7 for potential incompatibilities between MySQL 5.7 and 8.0, see Bit Functions and Operators, in MySQL 5.7 Reference Manual.

### Bit Operations Prior to MySQL 8.0

Bit operations prior to MySQL 8.0 handle only unsigned 64-bit
integer argument and result values (that is, unsigned
`BIGINT`

values). Conversion of
arguments of other types to `BIGINT`

occurs as necessary. Examples:

This statement operates on numeric literals, treated as unsigned 64-bit integers:

`mysql> SELECT 127 | 128, 128 << 2, BIT_COUNT(15); +-----------+----------+---------------+ | 127 | 128 | 128 << 2 | BIT_COUNT(15) | +-----------+----------+---------------+ | 255 | 512 | 4 | +-----------+----------+---------------+`

This statement performs to-number conversions on the string arguments (

`'127'`

to`127`

, and so forth) before performing the same operations as the first statement and producing the same results:`mysql> SELECT '127' | '128', '128' << 2, BIT_COUNT('15'); +---------------+------------+-----------------+ | '127' | '128' | '128' << 2 | BIT_COUNT('15') | +---------------+------------+-----------------+ | 255 | 512 | 4 | +---------------+------------+-----------------+`

This statement uses hexadecimal literals for the bit-operation arguments. MySQL by default treats hexadecimal literals as binary strings, but in numeric context evaluates them as numbers (see Section 9.1.4, “Hexadecimal Literals”). Prior to MySQL 8.0, numeric context includes bit operations. Examples:

`mysql> SELECT X'7F' | X'80', X'80' << 2, BIT_COUNT(X'0F'); +---------------+------------+------------------+ | X'7F' | X'80' | X'80' << 2 | BIT_COUNT(X'0F') | +---------------+------------+------------------+ | 255 | 512 | 4 | +---------------+------------+------------------+`

Handling of bit-value literals in bit operations is similar to hexadecimal literals (that is, as numbers).

### Bit Operations in MySQL 8.0

MySQL 8.0 extends bit operations to handle binary string arguments directly (without conversion) and produce binary string results. (Arguments that are not integers or binary strings are still converted to integers, as before.) This extension enhances bit operations in the following ways:

Bit operations become possible on values longer than 64 bits.

It is easier to perform bit operations on values that are more naturally represented as binary strings than as integers.

For example, consider UUID values and IPv6 addresses, which have human-readable text formats like this:

```
UUID: 6ccd780c-baba-1026-9564-5b8c656024db
IPv6: fe80::219:d1ff:fe91:1a72
```

It is cumbersome to operate on text strings in those formats. An
alternative is convert them to fixed-length binary strings without
delimiters. `UUID_TO_BIN()`

and
`INET6_ATON()`

each produce a value
of data type `BINARY(16)`

, a binary
string 16 bytes (128 bits) long. The following statements
illustrate this (`HEX()`

is used to produce
displayable values):

```
mysql> SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));
+----------------------------------------------------------+
| HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db')) |
+----------------------------------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------------------------------+
mysql> SELECT HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72'));
+---------------------------------------------+
| HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72')) |
+---------------------------------------------+
| FE800000000000000219D1FFFE911A72 |
+---------------------------------------------+
```

Those binary values are easily manipulable with bit operations to perform actions such as extracting the timestamp from UUID values, or extracting the network and host parts of IPv6 addresses. (For examples, see later in this discussion.)

Arguments that count as binary strings include column values,
routine parameters, local variables, and user-defined variables
that have a binary string type:
`BINARY`

,
`VARBINARY`

, or one of the
`BLOB`

types.

What about hexadecimal literals and bit literals? Recall that those are binary strings by default in MySQL, but numbers in numeric context. How are they handled for bit operations in MySQL 8.0? Does MySQL continue to evaluate them in numeric context, as is done prior to MySQL 8.0? Or do bit operations evaluate them as binary strings, now that binary strings can be handled “natively” without conversion?

Answer: It has been common to specify arguments to bit operations
using hexadecimal literals or bit literals with the intent that
they represent numbers, so MySQL continues to evaluate bit
operations in numeric context when all bit arguments are
hexadecimal or bit literals, for backward compatility. If you
require evaluation as binary strings instead, that is easily
accomplished: Use the `_binary`

introducer for at
least one literal.

These bit operations evaluate the hexadecimal literals and bit literals as integers:

`mysql> SELECT X'40' | X'01', b'11110001' & b'01001111'; +---------------+---------------------------+ | X'40' | X'01' | b'11110001' & b'01001111' | +---------------+---------------------------+ | 65 | 65 | +---------------+---------------------------+`

These bit operations evaluate the hexadecimal literals and bit literals as binary strings, due to the

`_binary`

introducer:`mysql> SELECT _binary X'40' | X'01', b'11110001' & _binary b'01001111'; +-----------------------+-----------------------------------+ | _binary X'40' | X'01' | b'11110001' & _binary b'01001111' | +-----------------------+-----------------------------------+ | A | A | +-----------------------+-----------------------------------+`

Although the bit operations in both statements produce a result
with a numeric value of 65, the second statement operates in
binary-string context, for which 65 is ASCII `A`

.

In numeric evaluation context, permitted values of hexadecimal literal and bit literal arguments have a maximum of 64 bits, as do results. By contrast, in binary-string evaluation context, permitted arguments (and results) can exceed 64 bits:

```
mysql> SELECT _binary X'4040404040404040' | X'0102030405060708';
+---------------------------------------------------+
| _binary X'4040404040404040' | X'0102030405060708' |
+---------------------------------------------------+
| ABCDEFGH |
+---------------------------------------------------+
```

There are several ways to refer to a hexadecimal literal or bit literal in a bit operation to cause binary-string evaluation:

`_binary `*literal*
BINARY *literal*
CAST(*literal* AS BINARY)

Another way to produce binary-string evaluation of hexadecimal literals or bit literals is to assign them to user-defined variables, which results in variables that have a binary string type:

```
mysql> SET @v1 = X'40', @v2 = X'01', @v3 = b'11110001', @v4 = b'01001111';
mysql> SELECT @v1 | @v2, @v3 & @v4;
+-----------+-----------+
| @v1 | @v2 | @v3 & @v4 |
+-----------+-----------+
| A | A |
+-----------+-----------+
```

In binary-string context, bitwise operation arguments must have
the same length or an
`ER_INVALID_BITWISE_OPERANDS_SIZE`

error occurs:

```
mysql> SELECT _binary X'40' | X'0001';
ERROR 3513 (HY000): Binary operands of bitwise
operators must be of equal length
```

To satisfy the equal-length requirement, pad the shorter value with leading zero digits or, if the longer value begins with leading zero digits and a shorter result value is acceptable, strip them:

```
mysql> SELECT _binary X'0040' | X'0001';
+---------------------------+
| _binary X'0040' | X'0001' |
+---------------------------+
| A |
+---------------------------+
mysql> SELECT _binary X'40' | X'01';
+-----------------------+
| _binary X'40' | X'01' |
+-----------------------+
| A |
+-----------------------+
```

Padding or stripping can also be accomplished using functions such
as `LPAD()`

,
`RPAD()`

,
`SUBSTR()`

, or
`CAST()`

. In such cases, the
expression arguments are no longer all literals and
`_binary`

becomes unnecessary. Examples:

```
mysql> SELECT LPAD(X'40', 2, X'00') | X'0001';
+---------------------------------+
| LPAD(X'40', 2, X'00') | X'0001' |
+---------------------------------+
| A |
+---------------------------------+
mysql> SELECT X'40' | SUBSTR(X'0001', 2, 1);
+-------------------------------+
| X'40' | SUBSTR(X'0001', 2, 1) |
+-------------------------------+
| A |
+-------------------------------+
```

### Binary String Bit-Operation Examples

The following example illustrates use of bit operations to extract parts of a UUID value, in this case, the timestamp and IEEE 802 node number. This technique requires bitmasks for each extracted part.

Convert the text UUID to the corresponding 16-byte binary value so that it can be manipulated using bit operations in binary-string context:

```
mysql> SET @uuid = UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db');
mysql> SELECT HEX(@uuid);
+----------------------------------+
| HEX(@uuid) |
+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------+
```

Construct bitmasks for the timestamp and node number parts of the value. The timestamp comprises the first three parts (64 bits, bits 0 to 63) and the node number is the last part (48 bits, bits 80 to 127):

```
mysql> SET @ts_mask = CAST(X'FFFFFFFFFFFFFFFF' AS BINARY(16));
mysql> SET @node_mask = CAST(X'FFFFFFFFFFFF' AS BINARY(16)) >> 80;
mysql> SELECT HEX(@ts_mask);
+----------------------------------+
| HEX(@ts_mask) |
+----------------------------------+
| FFFFFFFFFFFFFFFF0000000000000000 |
+----------------------------------+
mysql> SELECT HEX(@node_mask);
+----------------------------------+
| HEX(@node_mask) |
+----------------------------------+
| 00000000000000000000FFFFFFFFFFFF |
+----------------------------------+
```

The `CAST(... AS BINARY(16))`

function is used
here because the masks must be the same length as the UUID value
against which they are applied. The same result can be produced
using other functions to pad the masks to the required length:

```
SET @ts_mask= RPAD(X'FFFFFFFFFFFFFFFF' , 16, X'00');
SET @node_mask = LPAD(X'FFFFFFFFFFFF', 16, X'00') ;
```

Use the masks to extract the timestamp and node number parts:

```
mysql> SELECT HEX(@uuid & @ts_mask) AS 'timestamp part';
+----------------------------------+
| timestamp part |
+----------------------------------+
| 6CCD780CBABA10260000000000000000 |
+----------------------------------+
mysql> SELECT HEX(@uuid & @node_mask) AS 'node part';
+----------------------------------+
| node part |
+----------------------------------+
| 000000000000000000005B8C656024DB |
+----------------------------------+
```

The preceding example uses these bit operations: right shift
(`>>`

)
and bitwise AND
(`&`

).

`UUID_TO_BIN()`

takes a flag that
causes some bit rearrangement in the resulting binary UUID
value. If you use that flag, modify the extraction masks
accordingly.

The next example uses bit operations to extract the network and host parts of an IPv6 address. Suppose that the network part has a length of 80 bits. Then the host part has a length of 128 − 80 = 48 bits. To extract the network and host parts of the address, convert it to a binary string, then use bit operations in binary-string context.

Convert the text IPv6 address to the corresponding binary string:

`mysql> SET @ip = INET6_ATON('fe80::219:d1ff:fe91:1a72');`

Define the network length in bits:

`mysql> SET @net_len = 80;`

Construct network and host masks by shifting the all-ones address
left or right. To do this, begin with the address
`::`

, which is shorthand for all zeros, as you
can see by converting it to a binary string like this:

```
mysql> SELECT HEX(INET6_ATON('::')) AS 'all zeros';
+----------------------------------+
| all zeros |
+----------------------------------+
| 00000000000000000000000000000000 |
+----------------------------------+
```

To produce the complementary value (all ones), use the
`~`

operator
to invert the bits:

```
mysql> SELECT HEX(~INET6_ATON('::')) AS 'all ones';
+----------------------------------+
| all ones |
+----------------------------------+
| FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF |
+----------------------------------+
```

Shift the all-ones value left or right to produce the network and host masks:

```
mysql> SET @net_mask = ~INET6_ATON('::') << (128 - @net_len);
mysql> SET @host_mask = ~INET6_ATON('::') >> @net_len;
```

Display the masks to verify that they cover the correct parts of the address:

```
mysql> SELECT INET6_NTOA(@net_mask) AS 'network mask';
+----------------------------+
| network mask |
+----------------------------+
| ffff:ffff:ffff:ffff:ffff:: |
+----------------------------+
mysql> SELECT INET6_NTOA(@host_mask) AS 'host mask';
+------------------------+
| host mask |
+------------------------+
| ::ffff:255.255.255.255 |
+------------------------+
```

Extract and display the network and host parts of the address:

```
mysql> SET @net_part = @ip & @net_mask;
mysql> SET @host_part = @ip & @host_mask;
mysql> SELECT INET6_NTOA(@net_part) AS 'network part';
+-----------------+
| network part |
+-----------------+
| fe80::219:0:0:0 |
+-----------------+
mysql> SELECT INET6_NTOA(@host_part) AS 'host part';
+------------------+
| host part |
+------------------+
| ::d1ff:fe91:1a72 |
+------------------+
```

The preceding example uses these bit operations: Complement
(`~`

), left
shift
(`<<`

),
and bitwise AND
(`&`

).

The remaining discussion provides details on argument handling for each group of bit operations, more information about literal-value handling in bit operations, and potential incompatibilities between MySQL 8.0 and older MySQL versions.

### Bitwise AND, OR, and XOR Operations

For `&`

,
`|`

, and
`^`

bit
operations, the result type depends on whether the arguments are
evaluated as binary strings or numbers:

Binary-string evaluation occurs when the arguments have a binary string type, and at least one of them is not a hexadecimal literal, bit literal, or

`NULL`

literal. Numeric evaluation occurs otherwise, with argument conversion to unsigned 64-bit integers as necessary.Binary-string evaluation produces a binary string of the same length as the arguments. If the arguments have unequal lengths, an

`ER_INVALID_BITWISE_OPERANDS_SIZE`

error occurs. Numeric evaluation produces an unsigned 64-bit integer.

Examples of numeric evaluation:

```
mysql> SELECT 64 | 1, X'40' | X'01';
+--------+---------------+
| 64 | 1 | X'40' | X'01' |
+--------+---------------+
| 65 | 65 |
+--------+---------------+
```

Examples of binary-string evaluation:

```
mysql> SELECT _binary X'40' | X'01';
+-----------------------+
| _binary X'40' | X'01' |
+-----------------------+
| A |
+-----------------------+
mysql> SET @var1 = X'40', @var2 = X'01';
mysql> SELECT @var1 | @var2;
+---------------+
| @var1 | @var2 |
+---------------+
| A |
+---------------+
```

### Bitwise Complement and Shift Operations

For `~`

,
`<<`

, and
`>>`

bit
operations, the result type depends on whether the bit argument is
evaluated as a binary string or number:

Binary-string evaluation occurs when the bit argument has a binary string type, and is not a hexadecimal literal, bit literal, or

`NULL`

literal. Numeric evaluation occurs otherwise, with argument conversion to an unsigned 64-bit integer as necessary.Binary-string evaluation produces a binary string of the same length as the bit argument. Numeric evaluation produces an unsigned 64-bit integer.

For shift operations, bits shifted off the end of the value are lost without warning, regardless of the argument type. In particular, if the shift count is greater or equal to the number of bits in the bit argument, all bits in the result are 0.

Examples of numeric evaluation:

```
mysql> SELECT ~0, 64 << 2, X'40' << 2;
+----------------------+---------+------------+
| ~0 | 64 << 2 | X'40' << 2 |
+----------------------+---------+------------+
| 18446744073709551615 | 256 | 256 |
+----------------------+---------+------------+
```

Examples of binary-string evaluation:

```
mysql> SELECT HEX(_binary X'1111000022220000' >> 16);
+----------------------------------------+
| HEX(_binary X'1111000022220000' >> 16) |
+----------------------------------------+
| 0000111100002222 |
+----------------------------------------+
mysql> SELECT HEX(_binary X'1111000022220000' << 16);
+----------------------------------------+
| HEX(_binary X'1111000022220000' << 16) |
+----------------------------------------+
| 0000222200000000 |
+----------------------------------------+
mysql> SET @var1 = X'F0F0F0F0';
mysql> SELECT HEX(~@var1);
+-------------+
| HEX(~@var1) |
+-------------+
| 0F0F0F0F |
+-------------+
```

### BIT_COUNT() Operations

The `BIT_COUNT()`

function always
returns an unsigned 64-bit integer, or `NULL`

if
the argument is `NULL`

.

```
mysql> SELECT BIT_COUNT(127);
+----------------+
| BIT_COUNT(127) |
+----------------+
| 7 |
+----------------+
mysql> SELECT BIT_COUNT(b'010101'), BIT_COUNT(_binary b'010101');
+----------------------+------------------------------+
| BIT_COUNT(b'010101') | BIT_COUNT(_binary b'010101') |
+----------------------+------------------------------+
| 3 | 3 |
+----------------------+------------------------------+
```

### BIT_AND(), BIT_OR(), and BIT_XOR() Operations

For the `BIT_AND()`

,
`BIT_OR()`

, and
`BIT_XOR()`

bit functions, the result
type depends on whether the function argument values are evaluated
as binary strings or numbers:

Binary-string evaluation occurs when the argument values have a binary string type, and the argument is not a hexadecimal literal, bit literal, or

`NULL`

literal. Numeric evaluation occurs otherwise, with argument value conversion to unsigned 64-bit integers as necessary.Binary-string evaluation produces a binary string of the same length as the argument values. If argument values have unequal lengths, an

`ER_INVALID_BITWISE_OPERANDS_SIZE`

error occurs. If the argument size exceeds 511 bytes, an`ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE`

error occurs. Numeric evaluation produces an unsigned 64-bit integer.

`NULL`

values do not affect the result unless all
values are `NULL`

. In that case, the result is a
neutral value having the same length as the length of the argument
values (all bits 1 for `BIT_AND()`

,
all bits 0 for `BIT_OR()`

, and
`BIT_XOR()`

).

Example:

```
mysql> CREATE TABLE t (group_id INT, a VARBINARY(6));
mysql> INSERT INTO t VALUES (1, NULL);
mysql> INSERT INTO t VALUES (1, NULL);
mysql> INSERT INTO t VALUES (2, NULL);
mysql> INSERT INTO t VALUES (2, X'1234');
mysql> INSERT INTO t VALUES (2, X'FF34');
mysql> SELECT HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX(BIT_XOR(a))
FROM t GROUP BY group_id;
+-----------------+----------------+-----------------+
| HEX(BIT_AND(a)) | HEX(BIT_OR(a)) | HEX(BIT_XOR(a)) |
+-----------------+----------------+-----------------+
| FFFFFFFFFFFF | 000000000000 | 000000000000 |
| 1234 | FF34 | ED00 |
+-----------------+----------------+-----------------+
```

### Special Handling of Hexadecimal Literals, Bit Literals, and NULL Literals

For backward compatibility, MySQL 8.0 evaluates bit operations in
numeric context when all bit arguments are hexadecimal literals,
bit literals, or `NULL`

literals. That is, bit
operations on binary-string bit arguments do not use binary-string
evaluation if all bit arguments are unadorned hexadecimal
literals, bit literals, or `NULL`

literals. (This
does not apply to such literals if they are written with a
`_binary`

introducer,
`BINARY`

operator, or other way of
specifying them explicitly as binary strings.)

The literal handling just described is the same as prior to MySQL 8.0. Examples:

These bit operations evaluate the literals in numeric context and produce a

`BIGINT`

result:`b'0001' | b'0010' X'0008' << 8`

These bit operations evaluate

`NULL`

in numeric context and produce a`BIGINT`

result that has a`NULL`

value:`NULL & NULL NULL >> 4`

In MySQL 8.0, you can cause those operations to evaluate the arguments in binary-string context by indicating explicitly that at least one argument is a binary string:

```
_binary b'0001' | b'0010'
_binary X'0008' << 8
BINARY NULL & NULL
BINARY NULL >> 4
```

The result of the last two expressions is `NULL`

,
just as without the `BINARY`

operator, but the
data type of the result is a binary string type rather than an
integer type.

### Bit-Operation Incompatibilities with MySQL 5.7

Because bit operations can handle binary string arguments natively in MySQL 8.0, some expressions produce a different result in MySQL 8.0 than in 5.7. The five problematic expression types to watch out for are:

*nonliteral_binary* { & | ^ } *binary*
*binary* { & | ^ } *nonliteral_binary*
*nonliteral_binary* { << >> } *anything*
~ *nonliteral_binary*
*AGGR_BIT_FUNC*(*nonliteral_binary*)

Those expressions return `BIGINT`

in
MySQL 5.7, binary string in 8.0.

Explanation of notation:

`{`

: List of operators that apply to the given expression type.`op1`

... }`op2`

: Any kind of binary string argument, including a hexadecimal literal, bit literal, or`binary`

`NULL`

literal.: An argument that is a binary string value other than a hexadecimal literal, bit literal, or`nonliteral_binary`

`NULL`

literal.: An aggregate function that takes bit-value arguments:`AGGR_BIT_FUNC`

`BIT_AND()`

,`BIT_OR()`

,`BIT_XOR()`

.

For information about how to prepare in MySQL 5.7 for potential incompatibilities between MySQL 5.7 and 8.0, see Bit Functions and Operators, in MySQL 5.7 Reference Manual.

The following list describes available bit functions and operators:

Bitwise OR.

The result type depends on whether the arguments are evaluated as binary strings or numbers:

Binary-string evaluation occurs when the arguments have a binary string type, and at least one of them is not a hexadecimal literal, bit literal, or

`NULL`

literal. Numeric evaluation occurs otherwise, with argument conversion to unsigned 64-bit integers as necessary.Binary-string evaluation produces a binary string of the same length as the arguments. If the arguments have unequal lengths, an

`ER_INVALID_BITWISE_OPERANDS_SIZE`

error occurs. Numeric evaluation produces an unsigned 64-bit integer.

For more information, see the introductory discussion in this section.

`mysql> SELECT 29 | 15; -> 31 mysql> SELECT _binary X'40404040' | X'01020304'; -> 'ABCD'`

Bitwise AND.

The result type depends on whether the arguments are evaluated as binary strings or numbers:

Binary-string evaluation occurs when the arguments have a binary string type, and at least one of them is not a hexadecimal literal, bit literal, or

`NULL`

literal. Numeric evaluation occurs otherwise, with argument conversion to unsigned 64-bit integers as necessary.Binary-string evaluation produces a binary string of the same length as the arguments. If the arguments have unequal lengths, an

`ER_INVALID_BITWISE_OPERANDS_SIZE`

error occurs. Numeric evaluation produces an unsigned 64-bit integer.

For more information, see the introductory discussion in this section.

`mysql> SELECT 29 & 15; -> 13 mysql> SELECT HEX(_binary X'FF' & b'11110000'); -> 'F0'`

Bitwise XOR.

The result type depends on whether the arguments are evaluated as binary strings or numbers:

`NULL`

literal. Numeric evaluation occurs otherwise, with argument conversion to unsigned 64-bit integers as necessary.`ER_INVALID_BITWISE_OPERANDS_SIZE`

error occurs. Numeric evaluation produces an unsigned 64-bit integer.

For more information, see the introductory discussion in this section.

`mysql> SELECT 1 ^ 1; -> 0 mysql> SELECT 1 ^ 0; -> 1 mysql> SELECT 11 ^ 3; -> 8 mysql> SELECT HEX(_binary X'FEDC' ^ X'1111'); -> 'EFCD'`

Shifts a longlong (

`BIGINT`

) number or binary string to the left.The result type depends on whether the bit argument is evaluated as a binary string or number:

Binary-string evaluation occurs when the bit argument has a binary string type, and is not a hexadecimal literal, bit literal, or

`NULL`

literal. Numeric evaluation occurs otherwise, with argument conversion to an unsigned 64-bit integer as necessary.Binary-string evaluation produces a binary string of the same length as the bit argument. Numeric evaluation produces an unsigned 64-bit integer.

Bits shifted off the end of the value are lost without warning, regardless of the argument type. In particular, if the shift count is greater or equal to the number of bits in the bit argument, all bits in the result are 0.

For more information, see the introductory discussion in this section.

`mysql> SELECT 1 << 2; -> 4 mysql> SELECT HEX(_binary X'00FF00FF00FF' << 8); -> 'FF00FF00FF00'`

Shifts a longlong (

`BIGINT`

) number or binary string to the right.The result type depends on whether the bit argument is evaluated as a binary string or number:

Binary-string evaluation occurs when the bit argument has a binary string type, and is not a hexadecimal literal, bit literal, or

`NULL`

literal. Numeric evaluation occurs otherwise, with argument conversion to an unsigned 64-bit integer as necessary.Binary-string evaluation produces a binary string of the same length as the bit argument. Numeric evaluation produces an unsigned 64-bit integer.

Bits shifted off the end of the value are lost without warning, regardless of the argument type. In particular, if the shift count is greater or equal to the number of bits in the bit argument, all bits in the result are 0.

For more information, see the introductory discussion in this section.

`mysql> SELECT 4 >> 2; -> 1 mysql> SELECT HEX(_binary X'00FF00FF00FF' >> 8); -> '0000FF00FF00'`

Invert all bits.

The result type depends on whether the bit argument is evaluated as a binary string or number:

`NULL`

literal. Numeric evaluation occurs otherwise, with argument conversion to an unsigned 64-bit integer as necessary.

For more information, see the introductory discussion in this section.

`mysql> SELECT 5 & ~1; -> 4 mysql> SELECT HEX(~X'0000FFFF1111EEEE'); -> 'FFFF0000EEEE1111'`

Returns the number of bits that are set in the argument

as an unsigned 64-bit integer, or`N`

`NULL`

if the argument is`NULL`

.`mysql> SELECT BIT_COUNT(64), BIT_COUNT(BINARY 64); -> 1, 7 mysql> SELECT BIT_COUNT('64'), BIT_COUNT(_binary '64'); -> 1, 7 mysql> SELECT BIT_COUNT(X'40'), BIT_COUNT(_binary X'40'); -> 1, 1`

SELECT

SUM(IF(rubrik & 1, 1, 0)) actus,

SUM(IF(rubrik & 2, 1, 0)) shopping,

SUM(IF(rubrik & 4, 1, 0)) utils,

SUM(IF(rubrik & 8, 1, 0)) communication,

SUM(IF(rubrik & 16, 1, 0)) services,

COUNT(user_id) AS total,

FROM preferences p

Where rubrik is an integer.

select

ipAddress,

(ipAddress >> 24) as firstOctet,

(ipAddress>>16<<16)-(ipAddress>>24<<24)>>16 as secondOctet,

(ipAddress>>8<<8)-(ipAddress>>16<<16)>>8 as thirdOctet,

ipAddress-(ipAddress>>8<<8) as fourthOctet

from ips;

Result:

This way you don't need a string parser to separate the octets for display, drill down menus, sorting etc, and don't need to tear the octets apart with a string parser to put them in separate fields.

If these are your IP addresses I sincerely apologize. I just pulled them out of thin air to write this sql :D

There are 10 types of people in the world...

Sorry had to throw that in there 8)

There are probably "easier" ways to do this, but they aren't as efficient. I had a requirement for a tree view of ip addresses and this seems to be the easiest way to do it.

-Neil

ipAddress, (ipAddress >> 24) as firstOctet,

(ipAddress>>16) & 255 as secondOctet,

(ipAddress>>8) & 255 as thirdOctet,

ipAddress & 255 as fourthOctet

from ips;

Phillip.

If you want to turn on/off bit settings you do it like so, you need to do the &~ first and the | second.

Say prefs = 1636 (1024 | 512 | 64 | 32 | 4) and you want to turn off 512 and 64 and turn on 4 and not touch 1024

select prefs &~ 512 &~ 64 | 4;

1060

Even if prefs is 0 - you get the same result

Just make sure to put all the &~s first

In your case, could use SIGN() rather than IF. Or given that you are using SUM(), even could use >>:

SELECT

SUM(rubrik & 1) actus,

SUM(rubrik & 2)>>1 shopping,

SUM(rubrik & 4)>>2 utils,

SUM(rubrik & 8)>>3 communication,

SUM(rubrik & 16)>>4 services,

COUNT(user_id) AS total,

FROM preferences p

I have a database of customers who buy services from a company. The status of the customer tells me what service(s) they use and if they are current in buying that service. So for an example lets say my services include:

Pool Service, lawn mowing, window washing, tree trimming, house painting, mobile oil change, etc.. (you get the idea).

I set a number scheme for the status such as:

1 = Prospective Customer

2 = Cancelled Service

4 = Pool Service

8 = lawn mowing

16 = window washing

32 = tree trimming

64 = house painting

128 = mobile oil change

With this scheme a customer who needs pool service (4) and window washing (16) will have a status of 20 (4 + 16). A prospect (1) who might be interested in a mobile oil change (128) would have a status of 129 (1+128). etc...

Bitwise logic allows me to select customers who buy particular services based on their status value.

SELECT * FROM customers WHERE status & 1

//returns all prospects)

SELECT * FROM customers WHERE status & 16

//returns all of my window washing customers even if they are cancelled

SELECT * FROM customers WHERE (status & 16) AND !(status & 2)

//returns all of my window washing customers but not the cancelled ones

SELECT * FROM customers WHERE status & 36

//returns all of my tree trimming AND pool service customers i.e. 32+4

SELECT * FROM customers WHERE status & 10

//returns all cancelled lawn moving customers i.e. 2+8

SELECT * FROM customers WHERE (status & 32) AND !(status & 128)

//returns all tree trimming customers who do not get a mobile oil change

What u just described is a reroll of FIND_IN_SET and ENUM.