Mysql convert an int to MAC

问题:

I have some data that converts which has a 2 columns one column has IP and it contains values which are integers.I used the following function in my mysql query.Is there a function i can use to to convert my mac column which contains integers and data type is bigint to MAC address.

SELECT  INET_NTOA(ip_address) AS myip,mymac 
FROM table1

回答1:

Assuming that you have stored the MAC address by suppressing all separators and converting the resulting HEX number into int, the conversion from this int to a human readable MAC address would be:

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

The function is taken from http://www.onurguzel.com/storing-mac-address-in-a-mysql-database/

The MySQL version for this function:

delimiter $$
create function itomac (i BIGINT)
    returns char(20) 
    language SQL
begin
    declare temp CHAR(20);
    set temp = lpad (hex (i), 12, '0');
    return concat (left (temp, 2),':',mid(temp,3,2),':',mid(temp,5,2),':',mid(temp,7,2),':',mid(temp,9,2),':',mid(temp,11,2));
end;
$$
delimiter ;

You can also do it directly in SQL, like this:

select
    concat (left (b.mh, 2),':',mid(b.mh,3,2),':',mid(b.mh,5,2),':',mid(b.mh,7,2),':',mid(b.mh,9,2),':',mid(b.mh,11,2))
from (
    select lpad (hex (a.mac_as_int), 12, '0') as mh
    from (
        select 1234567890 as mac_as_int
    ) a
) b

回答2:

Just use HEX():

For a numeric argument N, HEX() returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number.

Therefore, in your case:

SELECT INET_NTOA(ip_address) AS myip, HEX(mymac)
FROM   table1

Note that this won't insert byte delimiters, such as colon characters.