Which type has INET6_ATON? / Max length of INET6_ATON?

Question

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.

Those are 16 bytes .... XX is 1 byte – Drew Jul 10 '16 at 16:40 — Drew, Jul 10 '16 at 16:40
@Drew Means I should use `CHAR(32)`? – HelloToYou Jul 10 '16 at 17:08 — HelloToYou, Jul 10 '16 at 17:08

score 0 · Accepted Answer · edited May 23 '17 at 12:32

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.

edited May 23 '17 at 12:32

Community

1
1

answered Jul 10 '16 at 17:21

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

Which type has INET6_ATON? / Max length of INET6_ATON?

1 Answers1