Use Mysql's Inet_aton () and Inet_ntoa () functions to store IP addresses in a way that shares _mysql

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 now use string char (15) to store IP addresses (16 bytes), using the Inet_aton () and Inet_ntoa () functions to store IP addresses efficiently, the application of unsigned int can meet the requirements, no need to use bigint, It takes only 4 bytes to save storage space and is much more efficient.

If the IP column has an index, you can query by using the following methods:

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 like operations, you can use the following methods:

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)