[Level 3] Enhance MySQL Performance to Store MAC Data with Stored Functions.

We know MySQL support inet_aton()/inet_ntoa() to convert ip address data to unsigned integer. These will reduce the data capacity and enhance performance. But MySQL seems not support MAC address.
So I think maybe I can create stored functions just like the purpose of inet_aton()/inet_ntoa() to also enhance performance.

By the way, stored function has a declaration option call "DETERMINISTIC". Default is "NOT DETERMINISTIC", it implies that the procedure may produce different results, given the same inputs. The alternative is "DETERMINISTIC", if the procedure always gives the same results given the same input. In this case, to let SQL parser can use the right index, we should declare the stored function with "DETERMINISTIC".
My  sample code as following:

delimiter //

## create stored function ether_atob(), from ascii to bit
## must define "deterministic", or explain will not use index
drop function if exists ether_atob//
create function ether_atob(sAscii char(17))
returns bit(48)
deterministic
begin
  declare bReturn bit(48);
  set bReturn=unhex(replace(sAscii,':',''));
  return bReturn;
end//

## create stored function ether_btoa(), from bit to ascii 
## must define "deterministic", or explain will not use index

drop function if exists ether_btoa//
create function 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 (ether_atob('00:CD:EF:00:CD:EF'),'00:CD:EF:00:CD:EF');
Query OK, 1 row affected (0.01 sec)

mysql> select ether_btoa(b), a from ether_table
where b=ether_atob('00:CD:EF:00:CD:EF');
+----------------+-------------------+
| 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 ether_btoa(b), a from ether_table where b=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 | ref  | b             | b    | 7       | const |    1 | Using where |
+----+-------------+-------------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Wish this helps.

regards,
Stanley Huang