CODEWITHSUNDEEP
mysqlbinary
user21616
user21616

Reputation: 35

Add binary(16) Ip value to MySql database

I am trying to populate a MySQL database field with a binary(16) value of an IP address and I'm getting:

Warning: #1265 Data truncated for column 'IP' at row 1

The binary value I am trying to insert is

00000000000000000000ffff5bd25452

What am I doing wrong?

MySql table schema

Upvotes: 1

Views: 1571

Answers (1)

Nabeel Ahmed
Nabeel Ahmed

Reputation: 19252

Currently you're losing data for using BINARY(16). To insert you need to unhex the value, for example:

INSERT INTO <your-table> (IP) VALUES(UNHEX("00000000000000000000ffff5bd25452"));

To get it back you'll be required to use HEX, to have the original form back:

SELECT HEX(IP) FROM <your-table>;

Edit 1: in apropos of your comment

how can I insert an IP string such as 107.180.58.16

As IPv4 addresses are 4 byte long, same as an INT (UNSIGNED):

Use INET_ATON to convert it to INT, and INET_NTOA to convert it back to IPv4 - for example:

INSERT INTO <your-table> (IP) VALUES (INET_ATON("107.180.58.16"));
SELECT INET_NTOA(IP) FROM <your-table>;

And, for IPv6 addresses i.e. 128 bits you can use 16 bytes binary field, BINARY(16) or VARBINARY(16).

Upvotes: 4

Related Questions