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

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:

type

Signed number value range

Unsigned value range

TINYINT (1 byte, 8 bits)

-128 ~ 127

0 ~ 255

SMALLINT (2 bytes, 16 bits)

-32768 ~ 32767

0 ~ 65535

MEDIUMINT (3 bytes, 24 bits)

-8388608 ~ 8388607

0 ~ 16777215

INT (4 bytes, 32 bits)

-2147483648 ~ 2147483647

0 ~ 4294967295

BIGINT (8 bytes, 64 bits)

-9223372036854775808 ~ 9223372036854775807

0 ~ 18446744073709551615

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.

<<:  A magical little tool that turns URL addresses into "ooooooooo"

>>:  Application of 5G in the Public Sector of Future Smart Cities

Recommend

Regarding the ocean, we actually have a choice...

There are ten thousand ways for us to live in pea...

What is missing for blockchain to be used commercially on a large scale?

I believe that many people have heard about the e...

You must know the five common misconceptions about HTTPS

Nowadays, the https protocol is widely valued and...

Urgent reminder: DediPath officially announced to run away

We have received the official announcement from D...

Worth learning! 10 good habits of network administrators

【51CTO.com Quick Translation】I have been a comic ...

A Preliminary Study on Kubernetes Network Concepts

Kubernetes network is a core concept in Kubernete...

Traditional Networks vs. IoT Networks: What’s the Difference?

IoT provides businesses with greater visibility, ...

Cloud empowers new life and Wind River IoT genes are upgraded again

There is a wind power plant abroad that mainly us...