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.
- What are the advantages and disadvantages of using DECIMAL(39,0) over the other methods such as 2*BIGINT?
- 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()?
We went for a
VARBINARY(16)column instead and use
inet_ntop()to do the conversions:
The functions can be loaded into a running MySQL server and will give you
INET6_PTONin SQL, just as the familiar
INET_ATONfunctions 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.