Php – How to convert IPv6 from binary for storage in MySQL

ipv6mysqlphp

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()?

Best Solution

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.

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

Previous versions can't do that with DATETIME...

But you can do it with TIMESTAMP:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB

Mysql – Which MySQL data type to use for storing boolean values

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

MySQL also states that:

We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

Best Solution

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

Mysql – Which MySQL data type to use for storing boolean values

Related Question