MySQL Tutorial/Data Types/Bit

BIT_COUNT(N)

Returns the number of bits that are set in the argument N.

   <source lang="sql">

SELECT BIT_COUNT(29), BIT_COUNT(b"101010");</source>

Bit-Field Values

bit-field values can be written using b"value" notation.

"value is a binary value written using zeros and ones.

   <source lang="sql">

mysql> mysql> CREATE TABLE t (b BIT(8)); Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT INTO t SET b = b"11111111"; Query OK, 1 row affected (0.01 sec) mysql> mysql> INSERT INTO t SET b = b"1010"; Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from t; +------+ | b | +------+ | � | |

   |

+------+ 2 rows in set (0.00 sec) mysql> mysql> drop table t; Query OK, 0 rows affected (0.00 sec)</source>

Bit Functions

Name Description BIT_COUNT() Return the number of bits that are set & Bitwise AND | Bitwise OR ^ Bitwise XOR << Left shift >> Right shift ~ Invert bits

BIT type column

M indicates the number of bits per value, from 1 to 64.

The default is 1 if M is omitted.

   <source lang="sql">

mysql> mysql> CREATE TABLE t (b BIT(8)); Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT INTO t SET b = b"11111111"; Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO t SET b = b"1010"; Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from t; +------+ | b | +------+ | � | |

   |

+------+ 2 rows in set (0.02 sec) mysql> mysql> drop table t; Query OK, 0 rows affected (0.00 sec)</source>

Bitwise AND(&): The result is an unsigned 64-bit integer.

   <source lang="sql">

mysql> mysql> mysql> SELECT 29 & 15; +---------+ | 29 & 15 | +---------+ | 13 | +---------+ 1 row in set (0.00 sec) mysql></source>

Bitwise OR(|): The result is an unsigned 64-bit integer

   <source lang="sql">

mysql> mysql> SELECT 29 | 15; +---------+ | 29 | 15 | +---------+ | 31 | +---------+ 1 row in set (0.00 sec) mysql> mysql></source>

Bitwise XOR(^): The result is an unsigned 64-bit integer

   <source lang="sql">

mysql> mysql> SELECT 1 ^ 1; +-------+ | 1 ^ 1 | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) mysql> SELECT 1 ^ 0; +-------+ | 1 ^ 0 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql> SELECT 11 ^ 3; +--------+ | 11 ^ 3 | +--------+ | 8 | +--------+ 1 row in set (0.02 sec) mysql></source>

Invert all bits :~ (The result is an unsigned 64-bit integer.)

   <source lang="sql">

mysql> mysql> mysql> SELECT 5 & ~1; +--------+ | 5 & ~1 | +--------+ | 4 | +--------+ 1 row in set (0.00 sec) mysql></source>

Shifts a number to the left (

   <source lang="sql">

mysql> mysql> mysql> SELECT 1 << 2; +--------+ | 1 << 2 | +--------+ | 4 | +--------+ 1 row in set (0.00 sec) mysql></source>

Shifts a number to the right:>> (The result is an unsigned 64-bit integer.)

   <source lang="sql">

mysql> mysql> mysql> SELECT 4 >> 2; +--------+ | 4 >> 2 | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) mysql></source>