This is the 7th day of my participation in the August Text Challenge.More challenges in August

preface

  • There may be some demand in the project or scenarios, such as the docking third-party requirements need to specify the request to the other party IP white list, for white list release or blacklist filter interception, this is where the persistent IP address to the local database, used to store the database has many, but in most cases scenarios using MySQL to store the IP address
  • In addition, MongoDB can also be used to store IP addresses. Redis can be used to limit interception when the system requests too much access frequency from the same IP address in a unit time

MySQL stores IP information:

  • Generally, IP addresses are stored in VARCHAR(15) columns in MySQL databases. The minimum length of AN IP address is 7 characters (0.0.0.1) and the maximum length is 15 characters (255.255.255.255).
  • In UFC-8 encoding, a Chinese character takes up 3 bytes, and an English character takes up 2 bytes. When MySQL uses variable-length string types, it also needs to use one byte to save the string length, while in MySQL, the int type takes up 4 bytes.

MySQL performance test reference

  • In section 4.1.7 of High Performance MySQL edition 3, it is recommended to use unsigned integer to store IP addresses (unsigned integer: MySQL int type signed storage range (-2147483648, 2147483647), unsigned means only store the range of positive numbers (0, 4294967295)).

Advantages of using int over string to store IP addresses:

  • 1. It can save the space occupied by storing data and index data
  • 2, integer can be more efficient use of range matching queries

There are some disadvantages as well as advantages:

  • The book also explains that IP addresses separated by decimal points are easy to read, so integer IP addresses are not as readable as strings and must be escaped

Escape IPv4 address at MySQL layer:

  • MySQL provides the INET_ATON() and INET_NTOA() functions for converting IPv4 addresses between integers and strings.
  • MySQL also provides INET6_ATON() and INET6_NTOA() functions for converting IPv6 addresses between integers and strings.

Compute escape IPv4 addresses at the business layer:

00000000 | 00000000 | 00000000 | 00000000

  • IP address in the computer is saved with 4 bytes, each byte with 8 bits of binary, so the total number of four numbers need 32 bits, so only need to string format IP address according to the decimal point split into 4 numbers, the table is saved in the binary 32 bits of the 4 segments
  • For example, the first value of the IP address moves 24 bits to the right, the second value moves 16 bits to the right, the third value moves 8 bits to the right, and the fourth value does not move. The final IP address is obtained by adding the four values
  • To resolve an integer IP address, simply do the reverse, using the bit operation to move unsigned 24, 16, and 8 bits to the left, respectively. Finally, the result can be spliced.
/** * @Author: ZRH * @Date: 2021/8/23 14:08 */ public final class IpLongUtils {/** ** convert string IP to long ** @param ipStr string IP * @return IP corresponding to long */ public static long ipToLong (String ipStr) { String[] ip = ipStr.split("\."); return (Long.valueOf(ip[0]) << 24) + (Long.valueOf(ip[1]) << 16) + (Long.valueOf(ip[2]) << 8) + Long.valueOf(ip[3]); } public static String longToIp (long) {public static String longToIp (long) {public static String longToIp (long) { { StringBuilder ip = new StringBuilder(); ip.append(ipLong >>> 24).append("."); ip.append((ipLong >>> 16) & 0xFF).append("."); ip.append((ipLong >>> 8) & 0xFF).append("."); ip.append(ipLong & 0xFF); return ip.toString(); } public static void main (String[] args) {system.out.println (iplongutils.iptolong ("255.255.255.255")); System.out.println(IpLongUtils.longToIp(4294967295L)); System. The out. Println (IpLongUtils. IpToLong (" 1.1.1.1 ")); System.out.println(IpLongUtils.longToIp(16843009L)); }} -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- print results: 4294967295 16843009 1.1.1.1 255.255.255.255Copy the code

The last

  • There are also some tricky operations, such as using four fields to hold each of the four IPv4 addresses, but this is only suitable for some specific scenarios or requirements
  • Learn with an open mind and make progress together