Use the inet_aton () and inet_ntoa () functions of mysql to store the IP address _ MySQL

Use the inet_aton () and inet_ntoa () functions of mysql to store the IP address bitsCN.com.

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 ('192. 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 ('2017. 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 |
| 1, 3362004721 | B |
| 1, 408494875 | C |
| 1, 1690836502 | D |
+ ------------ + ------ +
4 rows in set (0.00 sec)

Mysql> select * from jackbillow where ip = inet_aton ('2017. 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)

Currently, many applications use the character string char (15) to store IP addresses (occupying 16 bytes). Using the inet_aton () and inet_ntoa () functions, it is highly efficient to store IP addresses, the use of unsigned int can meet your needs. you do not need to use bigint, but only need 4 bytes to save storage space and improve efficiency.

If the IP column has an index, you can use the following query method:

Mysql> select inet_aton ('2017. 200.30.22 ');
+ ---------------------------- +
| Inet_aton ('2017. 200.30.22 ') |
+ ---------------------------- +
| 1, 1690836502 |
+ ---------------------------- +
1 row in set (0.00 sec)

Mysql> select * from jackbillow where ip = 1690836502;
+ ------------ + ------ +
| Ip | name |
+ ------------ + ------ +
| 1, 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 the 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 ') |
+ -------------------------- +
| 1, 3232235776 |
+ -------------------------- +
1 row in set (0.00 sec)

Mysql> select inet_aton ('192. 168.1.255 ');
+ ---------------------------- +
| Inet_aton ('192. 168.1.255 ') |
+ ---------------------------- +
| 1, 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)

BitsCN.com