[Original] Solution for IP operation in the database
Suitable for displaying the output IP, press in the IP range.
A SQL statement interchanges in the database in integer in the database
IP is stored in four bytes, the maximum ff.ff.ff.ff.ff.ff, points for the maximum of 255.255.255.255. We can understand that this is an integer and stored in the database, which reduces the reduction of the reduction in the form of storage, which is also conducive to the inquiries, and the database is a good habit.
The integer is displayed as a diverse decade:
I am using PHP and MySQL as an example, and my thoughts are: through the investigation IP, I found that four per person is 256, so that we can take out and add "." Click 10.
We can write such a function
Function SQL_LONG_TO_IP ($ Colum1) {Return "Concat (((". $ colum1. "- 8388608) / 16777216)% 256, '.' (((") (("$ colum1." - 32768) / 65536)% 256, ' ("$ colum1." - 128) / 256)% 256, '.', ("$ colum1.")% 256) AS ". $ colum1." "
Where $ Colum1 is the integer IP storage field
I have a view to analyze this function:
This function returns a string that turns the integer IP to a dot decimal string display. The input parameter is the column name of the integer IP. How to use: $ strsql = 'select'. SQL_LONG_TO_IP ('lip'). 'From ip_test;'. The $ strsq is executed and the display is the format of the dot.
Click on the decimal to IP:
Because the database is stored in an integer, it is necessary to turn the dot decimal decimal to the user in the query. As mentioned above, each bit is 256, so it is shifted or multiplied by 256 times. However, the Web makes such an arithmetic pressure, so you can use a Split function to remove each In addition, convert to 16 binding to add each insert in the form of 0xfffffffFF in the form of a SQL statement.
There is a list here, but it is not used to use the SPLIT function, because I didn't expect this function at that time: P.
// Convert IP to values, format: 0.0.0.0Function IP_TO_LONG ($ IP) {IF (Strlen> 0) {$ ip_array = expedition (".", $ IP); if ($ ip) ;; IP_ARRAY)! = 4) Return 0;} else {return 0;} $ start = 0; $ LEN = STRPOS ($ IP, '.') - $ Start; $ IP1 = Trim (Substr ($ IP, $ Start, $ LEN)) 0; $ Start = $ LEN 1; $ LEN = STRPOS ($ IP, '.', $ Start) - $ Start; $ IP2 = Trim (Substr ($ IP, $ Start, $ LEN ))) 0; $ start = $ LEN 1; $ LEN = STRPOS ($ IP, '.', $ Start) - $ Start; $ IP3 = Trim (Substr ($ IP, $ START, $ LEN)) 0; $ Start = $ LEN 1; $ LEN = Strlen ($ IP) - $ Start; $ IP4 = TRIM (Substr ($ IP, $ Start, $ LEN) 0; $ IP1 = Base_Convert ($ IP1, 10, 16); $ IP2 = Base_Convert ($ IP2, 10, 16); $ IP3 = Base_Convert ($ IP3, 10, 16); $ IP4 = Base_Convert ($ IP4, 10, 16); (Strlen) IP1) <2)? ($ IP1 = "0". $ IP1): ($ IP1); (Strlen ($ IP2) <2)? ($ IP2 = "0". $ IP2): ($ IP2); (Strlen ($ IP3) <2) "($ IP3 =" 0 ". $ IP3): ($ IP3); (Strlen) <2)? ($ IP4 =" 0 ". $ IP4): ( $ IP4); Return '0x'. $ IP1. $ IP2. $ IP3. $ IP4;} In this, you can already query and display the integer IP field.
