Use MySQL's Inet_aton () and Inet_ntoa () functions to share the method of storing IP addresses

Many applications currently apply string char (15) to store IP addresses (16 bytes), using the Inet_aton () and Inet_ntoa () functions to store IP addresses efficiently, suitable for unsigned int to meet demand, no need to use bigint, Requires only 4 bytes, saves storage space, and is much more efficient

Mysql> CREATE TABLE jackbillow (IP int unsigned, name char (1));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT into Jackbillow values (Inet_aton (' 192.168.1.200 '), ' A '), (Inet_aton (' 200.100.30.241 '), ' B ');
Query OK, 2 rows Affected (0.00 sec)
Records:2 duplicates:0 warnings:0

mysql> INSERT into Jackbillow values (Inet_aton (' 24.89.35.27 '), ' C '), (Inet_aton (' 100.200.30.22 '), ' D ');
Query OK, 2 rows Affected (0.00 sec)
Records:2 duplicates:0 warnings:0

Mysql> select * from Jackbillow;
+------------+------+
| IP | name |
+------------+------+
| 3232235976 | A |
| 3362004721 | B |
| 408494875 | C |
| 1690836502 | D |
+------------+------+
4 rows in Set (0.00 sec)

Mysql> SELECT * from jackbillow where IP = Inet_aton (' 192.168.1.200 ');
+------------+------+
| IP | name |
+------------+------+
| 3232235976 | A |
+------------+------+
1 row in Set (0.00 sec)

Mysql> Select Inet_ntoa (IP) from Jackbillow;
+----------------+
| Inet_ntoa (IP) |
+----------------+
| 192.168.1.200 |
| 200.100.30.241 |
| 24.89.35.27 |
| 100.200.30.22 |
+----------------+
4 rows in Set (0.00 sec)

Many applications currently apply string char (15) to store IP addresses (16 bytes), using the Inet_aton () and Inet_ntoa () functions to store IP addresses efficiently, suitable for unsigned int to meet demand, no need to use bigint, It takes only 4 bytes, saves storage space, and is much more efficient.

If the IP column has an index, you can query it in the following way:

Mysql> Select Inet_aton (' 100.200.30.22 ');
+----------------------------+
| Inet_aton (' 100.200.30.22 ') |
+----------------------------+
| 1690836502 |
+----------------------------+
1 row in Set (0.00 sec)

Mysql> SELECT * from Jackbillow where ip=1690836502;
+------------+------+
| IP | name |
+------------+------+
| 1690836502 | D |
+------------+------+
1 row in Set (0.00 sec)

Mysql> Select Inet_ntoa (IP), name from Jackbillow where ip=1690836502;
+---------------+------+
| Inet_ntoa (IP) | name |
+---------------+------+
| 100.200.30.22 | D |
+---------------+------+
1 row in Set (0.00 sec)

For a like operation, you can use the following method:

Mysql> Select Inet_ntoa (IP) from Jackbillow;
+----------------+
| Inet_ntoa (IP) |
+----------------+
| 192.168.1.200 |
| 200.100.30.241 |
| 24.89.35.27 |
| 100.200.30.22 |
| 192.168.1.100 |
| 192.168.1.20 |
| 192.168.2.20 |
+----------------+
7 Rows in Set (0.00 sec)

Mysql> Select Inet_aton (' 192.168.1.0 ');
+--------------------------+
| Inet_aton (' 192.168.1.0 ') |
+--------------------------+
| 3232235776 |
+--------------------------+
1 row in Set (0.00 sec)

Mysql> Select Inet_aton (' 192.168.1.255 ');
+----------------------------+
| Inet_aton (' 192.168.1.255 ') |
+----------------------------+
| 3232236031 |
+----------------------------+
1 row in Set (0.00 sec)

Mysql> Select Inet_ntoa (IP) from Jackbillow where IP between 3232235776 and 3232236031;
+---------------+
| Inet_ntoa (IP) |
+---------------+
| 192.168.1.200 |
| 192.168.1.100 |
| 192.168.1.20 |
+---------------+
3 Rows in Set (0.00 sec)

Mysql> Select Inet_ntoa (IP) from Jackbillow where IP between Inet_aton (' 192.168.1.0 ') and Inet_aton (' 192.168.1.255 ');
+---------------+
| Inet_ntoa (IP) |
+---------------+
| 192.168.1.200 |
| 192.168.1.100 |
| 192.168.1.20 |
+---------------+
3 Rows in Set (0.00 sec)

Use MySQL's Inet_aton () and Inet_ntoa () functions to share the method of storing IP addresses