IP processing functions inet_aton () and inet_ntoa ()

Int Conversion Function of MYSql IP Address
Select inet_aton (ip) from table_name;

Network Address:
192.168.33.123
Each value does not exceed 255, that is, the hexadecimal FF. The maximum value of two bytes is 255,
In this way, you can use a 32-bit integer to save this address.
192 168 33 123
1100 0000 1010 1000 0010 0001 0111
Combining these binary values is a 32-bit number.
11000000101010000010000101111011
Decimal:
3232244091
* INET_ATON (expr)
A "Point address" (such as 127.0.0.1) representing the network address of the string is provided, and an integer representing the address value is returned. The address can be 4 or 8 bits.
Mysql> SELECT INET_ATON ('2017. 207.224.40 ');
-> 3520061480
The number is always in the byte order of the network. In the preceding example, the number is calculated based on 209X2 ^ 24 + 207x2 ^ 16 + 224x2 ^ 8 + 40.
INET_ATON () can also understand the short format IP Address:

Mysql> SELECT INET_ATON ('2014. 0.0.1 '), INET_ATON ('2014. 1 ');
-> 2130706433,213 0706433
Note: When storing the value generated by INET_ATON (), we recommend that you use the int unsigned column. If you use the (Signed) INT column, the corresponding IP address value of the first eight-bit group greater than 127 will be as of 2147483647 (that is, INET_ATON ('123. returns the value ). See section 11.2, "value type ".
* INET_NTOA (expr)
Given a digital network address (4 or 8 bits), return the electrical address of the address as a string.
Mysql> SELECT INET_NTOA (3520061480 );

-> '2014. 207.224.40'

When working on a project, I don't know the two functions, so the IP address processing is all a function written by myself, but the validity rate is very poor. The following function converts the IP address to an integer:Copy codeThe Code is as follows: create function 'transiptoint' (ip char (31) RETURNS char (31)
Begin
DECLARE value1 CHAR (10 );
DECLARE value2 CHAR (10 );
DECLARE value3 CHAR (10 );
DECLARE value4 CHAR (10 );
Set value1 = SUBSTRING_INDEX (ip, '.', 1 );
Set value2 = SUBSTRING_INDEX (ip, '.', 2 );
Set value2 = SUBSTRING_INDEX (value2, '.',-1 );
Set value3 = SUBSTRING_INDEX (ip, '.',-2 );
Set value3 = SUBSTRING_INDEX (value3, '.', 1 );
Set value4 = SUBSTRING_INDEX (ip, '.',-1 );
Set value1 = value1 <24;
Set value2 = value2 <16;
Set value3 = value3 <8;
Return value1 + value2 + value3 + value4;
End;

For the convenience of comparing IP addresses, I also wrote a function to supplement each segment of an IP address by three digits, as shown below:Copy codeThe Code is as follows: create function 'fillip '(ip char (31) RETURNS char (31)
Begin
DECLARE value1 CHAR (31 );
DECLARE value2 CHAR (10 );
DECLARE value3 CHAR (10 );
DECLARE value4 CHAR (10 );
Set value1 = SUBSTRING_INDEX (ip, '.', 1 );
Set value2 = SUBSTRING_INDEX (ip, '.', 2 );
Set value2 = SUBSTRING_INDEX (value2, '.',-1 );
Set value3 = SUBSTRING_INDEX (ip, '.',-2 );
Set value3 = SUBSTRING_INDEX (value3, '.', 1 );
Set value4 = SUBSTRING_INDEX (ip, '.',-1 );
Set value1 = LPAD (value1, 3, '0 ');
Set value2 = LPAD (value2, 3, '0 ');
Set value3 = LPAD (value3, 3, '0 ');
Set value4 = LPAD (value4, 3, '0 ');
Return CONCAT (value1, '.', value2, '.', value3, '.', value4 );
End;

Below are some supplements
* INET_ATON (expr)
A "Point address" (such as 127.0.0.1) representing the network address of the string is provided, and an integer representing the address value is returned. The address can be 4 or 8 bits.
Mysql> SELECT INET_ATON ('2017. 207.224.40 ');
-> 3520061480
The number is always in the byte order of the network. In the preceding example, the number is calculated based on 209X2563 + 207x2562 + 224x256 + 40.
INET_ATON () can also understand the short format IP Address:
Mysql> SELECT INET_ATON ('2014. 0.0.1 '), INET_ATON ('2014. 1 ');
-> 2130706433,213 0706433
Note: When storing the value generated by INET_ATON (), we recommend that you use the int unsigned column. If you use the (Signed) INT column, the corresponding IP address value of the first eight-bit group greater than 127 will be as of 2147483647 (that is, INET_ATON ('123. returns the value ). See "Value Type" in the MySQL document ".
* INET_NTOA (expr)
Given a digital network address (4 or 8 bits), return the electrical address of the address as a string.
*
Mysql> SELECT INET_NTOA (3520061480 );
-> '2014. 207.224.40'

Bytes --------------------------------------------------------------------------------------------------

Integer fields are much more efficient than strings, which also conforms to an optimization principle: the most appropriate (minimum) and simplest data type for field type definition.
The inet_aton () algorithm actually uses the ip number used in international ip address differentiation.
The ip number of a. B. c. d is:
A * 256 Power 3 + B * 256 power 2 + c * 256 power 1 + d * 256 power 0.

Bytes --------------------------------------------------------------------------------------------------

Use mysql built-in functions to handle timestamp Problems
Eg: select FROM_UNIXTIME (UNIX_TIMESTAMP (), '% Y % D % M % h: % I: % s % x ');
Results: 2004 3rd August 03:35:48 2004