I am confused. If I want to store IP addresses, what data type is better?

When it comes to IP addresses (IPv4), common IP addresses such as 192.168.0.1 and 127.0.0.1 will immediately come to mind. Then, considering the question "What data type is used to store IP addresses in MySQL?", the char string type will be used.

The interviewer looked indifferent, and you immediately realized that something was wrong and thought about it carefully.

Then I found that the length of this IP address varies. The shortest can be 0.0.0.0, which only requires 7 bits, and the longest can be 255.255.255.255, which requires 15 bits. So I confidently answered to use varchar(15) to store the IP address, and secretly delighted myself that I could think of this.

Unexpectedly, the interviewer smiled contemptuously and asked you "Are you sure?" You thought that the interviewer was testing you, so you answered firmly "Yes".

Then we started the next question

......

People often use varchar(15) columns to store IP addresses, but this is not the optimal solution.

The essence of an IP address is a 32-bit unsigned integer. The dotted decimal string format like 192.168.0.1 is just to help people understand and remember. The decimal representation of 192.168.0.1 is the unsigned integer 3232235521.

Therefore, when people say that IP addresses are stored in string type, they actually subconsciously think that IP addresses are strings and store dotted decimal strings, but the correct method is to store 32-bit unsigned integers.

The so-called signed number actually uses the highest bit as the sign bit. For example, for a 32-bit signed INT, the highest bit is the sign bit, and the remaining 31 bits are the actual value. Therefore, the value range of a signed INT is:

The value range of unsigned INT is:

The following table lists the signed and unsigned value ranges of various integer types in MySQL. When defining a table, you can add the keyword UNSIGNED after the data type to define an unsigned integer, otherwise the default is a signed integer:

Combined with the above table, we can see that a 32-bit unsigned INT can just hold an IPv4 address. The following is a comparison between the two data types: INT UNSIGNED and VARCHAR(15):

1. Storage space: 4-byte INT type and 15-byte VARCHAR(15) save more storage space. In addition, VARCHAR not only saves the required number of characters, but also adds one byte to record the length (if the length declared for the column exceeds 255, two bytes are used to record the length), so VARCHAR(15) actually takes up 16 bytes.
2. Retrieval speed: If we want to create an index on the IP address, then for the string index, the retrieval speed of the integer index is simply a downgrade, because the comparison of the string type needs to start from the first character and traverse in sequence, which is slower.

MySQL very considerately provides the conversion functions between IPv4 address dotted decimal and unsigned integer, inet_aton and inet_ntoa (the underlying operation is binary shift, which is very fast):

Of course, you should perform these conversions in your business to reduce the pressure on MySQL.