0

I wanted to know how to save an INET6_ATON result to MYSQL. So I've read the MYSQL-Help article and it says, I should use VARBINARY(16). But now, with an IPv4-Address it has the content 0x7F000001 and I'm unable to get results using SQL. My idea was to use CHAR, but in this case I don't know what's the maximum length of an INET6_ATON-result.

So: How to get MYSQL-results if the result is saved as VARBINARY? Or otherwise: What's the maximum length of an INET6_ATON-result?

I'm converting the IP-Addresses using this SQL-Statement:

SELECT HEX(INET6_ATON("FE80:0000:0000:0000:0202:B3FF:FE1E:8329"))

Thanks a lot.

HelloToYou
  • 335
  • 5
  • 14

1 Answers1

0

Note that the human readable column below is for human consumption.

The inet6 column will contain out-of-range numbers should I say to make much sense to humans. Goobly goop, if you will.

create table myFriends
(   id int auto_increment primary key,
    friendlyName varchar(100) not null,
    inet6 binary(16) not null,
    humanReadable char(32) not null
);

insert myFriends (friendlyName,inet6,humanReadable) values 
('Kathy Higgins',INET6_ATON("FE80:0000:0000:0000:0202:B3FF:FE1E:8329"),HEX(INET6_ATON("FE80:0000:0000:0000:0202:B3FF:FE1E:8329")));

select * from myFriends;
+----+---------------+------------------+----------------------------------+
| id | friendlyName  | inet6            | humanReadable                    |
+----+---------------+------------------+----------------------------------+
|  1 | Kathy Higgins | ■Ç      ☻☻│ ■▲â) | FE800000000000000202B3FFFE1E8329 |
+----+---------------+------------------+----------------------------------+

FE80 represents 2 bytes. FE 80. Hexadecimal. Each byte ranges from 00 to FF (255).

Check out my answer Here on different formats. Often nearly duplicate info is used in one table.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • What is more efficient for MYSQL? – HelloToYou Jul 10 '16 at 17:29
  • More efficient is the varbinary(16) as that is what a decent join would be on. Thinner is better. It depends in what context you use it for functions as it relates to usefulness to you. – Drew Jul 10 '16 at 17:31
  • What about IPv4? For example, `SELECT * FROM myFriends WHERE ipv4 = 0x7F000001` doesn't work. – HelloToYou Jul 10 '16 at 17:33
  • Check out the edit at the bottom of my answer. Play with it for a while. – Drew Jul 10 '16 at 17:34
  • I changed the answer to `binary(16)`. Determine which is more appropriate for *your* setup and usage depending on padding. There is overhead for var this and that. If it is always generating 16 bytes, go with `binary(16)` and not `varbinary(16)` – Drew Jul 10 '16 at 17:38