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 “0123456789AB”), 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)));
}
  • David

    This would save me a lot of time. Thank you.

  • Great! Thank you very much, love when with the solution come the explanation to!

  • Miquel Àngel Daniel

    I’m using this procedure to store mac-address. Now i’m having troubles, but still don’t know which problem is. My question is. Using this conversion can happen that two different mac address finally has the same bigint value?