Onur Güzel This was a triumph…

Storing MAC address in a MySQL database

Today, I encountered a problem: storing MAC address in a MySQL database. MAC addresses (something like "01:23:45:67:78:AB") contain six hexadecimal (base 16) values. This means each hexadecimal field can store 256 (2 to the power of 8, in base 10) values. It's 8 bits (= 1 byte) for each value.

6 field x 8 bit = 48 bit = 6 bytes

Thus, instead of storing the address as string, I prefer to store it as an integer. Because as a string (without colons "0123456789A"), it can be stored in 12 bytes. Storing MAC address as integer allows us to store it using less space and also fast indexing.

In database, we can use unsigned BIGINT data type as it can store up to 8 bytes. While reading from database,

SELECT HEX(mac_addr) FROM `devices`;

While saving,

INSERT INTO `devices` (mac_addr) VALUES (x'0123456789AB');

If you prefer to make conversions in PHP,

function mac2int($mac) {
    return base_convert($mac, 16, 10);
}

function int2mac($int) {
    return base_convert($int, 10, 16);
}

You can use this function to make the address human readable

function int2macaddress($int) {
$hex = base_convert($int, 10, 16);
while (strlen($hex) < 12)
	$hex = '0'.$hex;
return strtoupper(implode(':', str_split($hex,2)));
}