1

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

user21616
  • 35
  • 7
  • 1
    The "binary" value you are trying to insert is actually a hexadecimal value, consisting of at least `12x4 = 48` bits. Is your binary column wide enough to store this value? Show us your table schema please. – Tim Biegeleisen Apr 24 '17 at 05:23
  • I'm using phpMyAdmin and this is one of the values already inserted in the previous fields. I have added a picture with the MySQL schema. – user21616 Apr 24 '17 at 05:59

1 Answers1

4

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).

Community
  • 1
  • 1
Nabeel Ahmed
  • 18,328
  • 4
  • 58
  • 63
  • The correct SQL syntax should be `INSERT INTO (IP) VALUES(UNHEX("00000000000000000000ffff5bd25452"));` but I still failed to understant how can I insert an IP string such as `107.180.58.16` for example – user21616 Apr 24 '17 at 14:50