Monday, January 16, 2012

[Level 3] rw_ether_atob, rw_ether_btoa stored function in MySQL.

delimiter //

drop function if exists rw_ether_atob//
create function rw_ether_atob(sAscii char(17))
returns bit(48)
deterministic
begin
  declare bReturn bit(48);
  ##set bReturn=conv(replace(sAscii,':',''),16,2); ## string output not bit
  ##set bReturn=conv(replace(sAscii,':',''),16,10); ## not work
  ##set bReturn=cast(replace(sAscii,':','') as bit(48)); ## syntax error
  ##set bReturn=bin(replace(sAscii,':','')); ## syntax error
  set bReturn=unhex(replace(sAscii,':',''));
  return bReturn;
end//

drop function if exists rw_ether_btoa//
create function rw_ether_btoa(sBit bit(48))
returns char(17)
deterministic
begin
  declare sReturn char(17);
  set sReturn=lpad(hex(sBit),12,'0');
  set sReturn=concat_ws(':', substr(sReturn,1,2), substr(sReturn,3,2), substr(sReturn,5,2), substr(sReturn,7,2), substr(sReturn,9,2), substr(sReturn,11,2));
  return sReturn;
end//

delimiter ;

/*
mysql> create table ether_table (b bit(48), a char(17), index(b), index(a));
Query OK, 0 rows affected (0.67 sec)

mysql> insert into ether_table values (rw_ether_atob('00:CD:EF:00:CD:EF'),'00:CD:EF:00:CD:EF'); 
Query OK, 1 row affected (0.01 sec)

mysql> select rw_ether_btoa(b), a from ether_table
    -> where b=rw_ether_atob('00:CD:EF:00:CD:EF');
+-------------------+-------------------+
| rw_ether_btoa(b)  | a                 |
+-------------------+-------------------+
| 00:CD:EF:00:CD:EF | 00:CD:EF:00:CD:EF |
+-------------------+-------------------+
1 rows in set (0.01 sec)

mysql> explain select rw_ether_btoa(b), a from ether_table
    -> where b=rw_ether_atob('00:CD:EF:00:CD:EF');
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ether_table | ALL  | NULL          | NULL | NULL    | NULL | 1002 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

*/
Wish this helps. regards, Stanley Huang