Storing IPv6 Addresses in MySQL

by

How about:

BINARY(16)

That should be effective enough.

Currently there is no function to convert textual IPv6 addresses from/to binary in the MySQL server, as noted in that bug report. You either need to do it in your application or possibly make a UDF (User-Defined Function) in the MySQL server to do that.

UPDATE:

MySQL 5.6.3 has support for IPv6 addresses, see the following: “INET6_ATON(expr)“.

The data type is VARBINARY(16) instead of BINARY(16) as I suggested earlier. The only reason for this is that the MySQL functions work for both IPv6 and IPv4 addresses. BINARY(16) is fine for storing only IPv6 addresses and saves one byte. VARBINARY(16) should be used when handling both IPv6 and IPv4 addresses.

An implementation for older versions of MySQL and MariaDB, see the following: “EXTENDING MYSQL 5 WITH IPV6 FUNCTIONS“.

More Related Contents:

  1. INSERT INTO SELECT statement with more columns which is not in another table
  2. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  3. 1052: Column ‘id’ in field list is ambiguous
  4. How to split the name string in mysql?
  5. Count the occurrences of DISTINCT values
  6. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  7. Cumulative sum over a set of rows in mysql
  8. How to set a maximum execution time for a mysql query?
  9. Set NOW() as Default Value for datetime datatype?
  10. Add Auto-Increment ID to existing table?
  11. MySQL SELECT DISTINCT multiple columns
  12. How to copy a row and insert in same table with a autoincrement field in MySQL?
  13. SQL DELETE with JOIN another table for WHERE condition
  14. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  15. Using group by on two fields and count in SQL
  16. Can MySQL convert a stored UTC time to local timezone?
  17. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  18. MySQL CONCAT returns NULL if any field contain NULL
  19. Does Order of Fields of Multi-Column Index in MySQL Matter
  20. Integrity constraint violation: 1452 Cannot add or update a child row:
  21. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  22. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  23. How to get a list of months between two dates in mysql
  24. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  25. Postgres LEFT JOIN with WHERE condition
  26. How to format number with “.” as thousands separator and “,” as decimal separator?
  27. Using DISTINCT and COUNT together in a MySQL Query
  28. How to check if field is null or empty in MySQL?
  29. MySQL OPTIMIZE all tables?
  30. Deleting a row based on the max value

Leave a Comment