The conversion function of MySQL IP to int
Select Inet_aton (IP) from table_name;
Network address:
192.168.33.123
The maximum of each value does not cross 255, which is hexadecimal FF, and the maximum value of two byte is 255.
This way, you can use a 32-bit plastic to save this address.
192 168 33 123
1100 0000 1010 1000 0010 0001 0111 1011
This binary combination is a 32-digit number.
11000000101010000010000101111011
Decimal is
3232244091
* Inet_aton (expr)
Gives a "point address" (such as 127.0.0.1) of a network address as a string that returns an integer representing the value of the address. The address can be a 4-or 8-bit address.
mysql> SELECT Inet_aton (' 209.207.224.40 ');
-> 3520061480
The resulting numbers are always in the network byte order. As the above example, the number is calculated according to 209x2^24 + 207x2^16 + 224x2^8 + 40.
Inet_aton () can also understand short form IP addresses:
mysql> SELECT Inet_aton (' 127.0.0.1 '), Inet_aton (' 127.1 ');
-> 2130706433, 2130706433
Note: When storing values produced by Inet_aton (), it is recommended that you use an INT UNSIGNED column. If you use a (signed) int column, the corresponding IP address value of the first eight-bit group greater than 127 will be the value returned by 2147483647 (that is, Inet_aton (' 127.255.255.255 ')). See section 11.2, "Numeric type."
* INET_NTOA (expr)
Given a digital network address (4 or 8 bits), returns the electrical address representation of the address as a string.
Mysql> SELECT Inet_ntoa (3520061480);
-> ' 209.207.224.40 '
When doing the project, do not know these two functions, so the IP processing is their own write function, but the efficiency is very poor, the following function is to convert IP into an integer:
Copy Code code as follows:
CREATE FUNCTION ' transiptoint ' (IP char ()) 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 the IP address, I also wrote a function to make up three bits in each paragraph of the IP address, as follows:
Copy Code code as follows:
CREATE FUNCTION ' fillip ' (IP char ()) 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
here are some additional
* Inet_aton (expr)
Gives a "point address" (such as 127.0.0.1) of a network address as a string that returns an integer representing the value of the address. The address can be a 4-or 8-bit address.
mysql> SELECT Inet_aton (' 209.207.224.40 ');
-> 3520061480
The resulting numbers are always in the network byte order. As the above example, the number is calculated according to 209x2563 + 207x2562 + 224x256 + 40.
Inet_aton () can also understand short form IP addresses:
mysql> SELECT Inet_aton (' 127.0.0.1 '), Inet_aton (' 127.1 ');
-> 2130706433, 2130706433
Note: When storing values produced by Inet_aton (), it is recommended that you use an INT UNSIGNED column. If you use a (signed) int column, the corresponding IP address value of the first eight-bit group greater than 127 will be the value returned by 2147483647 (that is, Inet_aton (' 127.255.255.255 ')). See "Numeric Types" in the MySQL documentation.
* INET_NTOA (expr)
Given a digital network address (4 or 8 bits), returns the electrical address representation of the address as a string.
*
Mysql> SELECT Inet_ntoa (3520061480);
-> ' 209.207.224.40 '
--------------------------------------------------------------------------------------------------
An integral field is a lot more efficient than a string, which is also consistent with an optimization principle: The field type definition uses the most appropriate (minimal) and simplest data type.
Inet_aton () algorithm, in fact, borrowed from the international IP address in the distinction between the use of IP number.
The IP number for A.B.C.D is:
A * 256 of 3 times + b * 256 of 2 square + c * 256 of 1 of square + D * 256 of the 0 times.
--------------------------------------------------------------------------------------------------
Using MySQL's built-in functions to handle timestamp problems
Eg:select From_unixtime (Unix_timestamp (), '%Y%d%m%h:%i:%s%x ');
Result: 3rd August 03:35:48 2004
