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:
<<
Example: MySQL Left Shift operator
The following MySQL statement will return an unsigned integer for numbers specified in the argument.
SELECT 2 <<8;
Sample 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;
Sample 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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook