Mysql将一个int转换为MAC [英] Mysql convert an int to MAC
问题描述
我有一些数据转换,其中有2列一列有IP,它包含的值是整数。我在我的mysql查询中使用了以下函数。是否有我可以用来转换我的mac列的函数包含整数和数据类型是bigint到MAC地址。
SELECT INET_NTOA(ip_address)AS myip,mymac
FROM table1
假设您通过抑制所有分隔符并将生成的HEX编号转换为int,则从此int到人类可读MAC地址的转换将为:
函数int2macaddress ($ int){
$ hex = base_convert($ int,10,16);
while(strlen($ hex)< 12)
$ hex ='0'。$ hex;
return strtoupper(implode(':',str_split($ hex,2)));
}
该函数取自 http://www.onurguzel.com/storing-mac-address-in-a-mysql-database/ 此函数的MySQL版本: 您也可以直接在SQL中执行此操作,如下所示: 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.
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: The function is taken from http://www.onurguzel.com/storing-mac-address-in-a-mysql-database/ The MySQL version for this function: You can also do it directly in SQL, like this:
这篇关于Mysql将一个int转换为MAC的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
分隔符$$
创建函数itomac(i BIGINT)
返回char(20)
语言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), ':',中(温度,9,2), ':',中(温度,11,2));
end;
$$
分隔符;
select
concat(left(b.mh,2),':',mid(b.mh,3,2),':',中期(b.mh,5,2), ':',中间(b.mh,7,2), ':',中间(b.mh,9,2), ':',中间(b.mh ,11,2))
从(
)选择lpad(十六进制(a.mac_as_int),12,'0')作为mh
从(
)选择1234567890作为mac_as_int
)a
)b
SELECT INET_NTOA(ip_address) AS myip,mymac
FROM table1
function int2macaddress($int) {
$hex = base_convert($int, 10, 16);
while (strlen($hex) < 12)
$hex = '0'.$hex;
return strtoupper(implode(':', str_split($hex,2)));
}
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 ;
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