php - How to convert IPv6 from binary for storage in MySQL

Question

I am trying to store IPv6 addresses in MySQL 5.0 in an efficient way. I have read the other questions related to this, such as this one. The author of that question eventually chose for two BIGINT fields. My searches have also turned up another often used mechanism: Using a DECIMAL(39,0) to store the IPv6 address. I have two questions about that.

1. What are the advantages and disadvantages of using DECIMAL(39,0) over the other methods such as 2*BIGINT?
2. How do I convert (in PHP) from the binary format as returned by inet_pton() to a decimal string format usable by MySQL, and how do I convert back so I can pretty-print with inet_ntop()?

Answer 1

We went for a VARBINARY(16) column instead and use inet_pton() and inet_ntop() to do the conversions:

https://github.com/skion/mysql-udf-ipv6

The functions can be loaded into a running MySQL server and will give you INET6_NTOP and INET6_PTON in SQL, just as the familiar INET_NTOA and INET_ATON functions for IPv4.

Edit: There are compatible functions in MySQL now, just with different names. Only use the above if you are on pre-5.6 MySQL and are looking for a convenient future upgrade path.