MySQL Tutorial/Data Types/Bit

BIT_COUNT(N)

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

SELECT BIT_COUNT(29), BIT_COUNT(b"101010");

Bit-Field Values

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

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

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)

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.

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)

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

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

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

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

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

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>

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

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

Shifts a number to the left (

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

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

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