Add binary(16) Ip value to MySql database

Question

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

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
– Tim Biegeleisen, Commented Apr 24, 2017 at 5: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
– user21616, Commented Apr 24, 2017 at 5:59

Community · Accepted Answer · 2017-05-23 11:54:25Z

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

How to store an IP in mySQL

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Apr 24, 2017 at 6:44

Nabeel Ahmed

19.5k4 gold badges61 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user21616 Over a year ago

The correct SQL syntax should be INSERT INTO <your-table> (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

Collectives™ on Stack Overflow

Add binary(16) Ip value to MySql database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related