[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

Comments

Post a Comment

Popular posts from this blog

[Level 2] Shell Interactive With Zenity Episode 5 -- list

Image
If you want to give a list for user to choose, you can use the option "--list". The command synopsis as below: SYNOPSIS      zenity  --list [--title=title] [--window-icon=path] [--width=width]    [--height=height] [--timeout=seconds] [--column=text] [--checklist]       [--radiolist] [--separator=character] [--multiple] [--editable] [--print-column=number] [--hide-column=number] Just like " --calendar ", if you press "ESC" key or click button "Cancel", the return code will be "1". If you select one option and click "OK", zenity will response the option and return the code will be "0". And also the same effect about the options "--title", "--timeout"...etc, for saving your time, please refer to the previous post " [Level 2] Shell Interactive With Zenity Episode 1 -- calendar ", too. About "--multiple" and "--separator", please refer to tht previous post " [Lev...
Read more

[Level 2] iif in Python

If you want to implement iif in Python, please refer to the following sample code: #!/bin/env python def iif(b): return 'It\'s True' if b else 'It\'s False' print iif(True) print iif(False) And the following sample, guess what will you get! #!/bin/env python b = True print 'b: %s' % 'Ture' if b else 'False' b = False print 'b: %s' % 'Ture' if b else 'False' Result: $ ./iif.py b: Ture False The second result is "False", not "b: False". Why??? Because the prededence of string concatenation is higher than if. Therefore you need to use () to make it correct. #!/bin/env python b = True print 'b: %s' % 'Ture' if b else 'False' b = False print 'b: %s' % ('Ture' if b else 'False') Wish this helps. regards, Stanley Huang
Read more