MySQL Left shift and Right shift Operators

Left Shift operator

MySQL Left shift operator returns an unsigned 64-bit integer. The return value will be zero when the shift count is greater than or equal to the width of a 64-bit unsigned number. It shifts the BIGINT number to the left.

Syntax:

<<

MySQL Version: 8.0

Example: MySQL Left Shift operator

The following MySQL statement will return an unsigned integer for numbers specified in the argument.

SELECT 2 <<8;

Output:

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

MySQL Right Shift operator

MySQL Right shift operator returns an unsigned 64 bit integer. The return value will be zero when the shift count is greater than or equal to the width of a 64 bit unsigned number. It shifts the BIGINT number to the right.

Syntax

 >>

Example: MySQL Right Shift operator

The following MySQL statement will return an unsigned integer for numbers specified in the argument.

SELECT  2 >>8;

Output:

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

Extract the octets from 32 bit unsigned stored IP addresses

If you store your ip addresses as a 32 bit unsigned integer using INET_ATON() function, you can use bitwise operators to return the IP address from its numeric value. Here is the query :

mysql> SELECT * FROM ipdata;
+------------+
| ipaddress  |
+------------+
| 2130706433 |
| 2921711528 |
| 3056763172 |
+------------+
3 rows in set (0.00 sec)
mysql> 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 ipdata;
+------------+------------+-------------+------------+-------------+
| ipaddress  | FirstOctet | SecondOctet | ThirdOctet | FourthOctet |
+------------+------------+-------------+------------+-------------+
| 2130706433 |        127 |           0 |          0 |           1 |
| 2921711528 |        174 |          37 |        199 |         168 |
| 3056763172 |        182 |          50 |        129 |          36 |
+------------+------------+-------------+------------+-------------+
3 rows in set (0.02 sec)

Note: You can use INET_NTOA() functions to get the same result.

Previous: BITWISE XOR
Next: MySQL Full-Text Search Functions