mleiv.com

There are three options for storing a MAC address in a database column:

• CHAR(12) - takes up a great deal of space and is inappropriate for indexes or unique keys.
• BIGINT - requires converting between hexadecimal and decimal every time you store or query data.
• Supporting Table ID - with a secondary CHAR(12) column. It requires a join for all queries, and may still require a BIGINT ID column depending on the number of different MAC addresses expected. Also any uniqueness constraint also runs up against the problems listed for CHAR(12).

The mysql database I created last year was over a billion rows per month and had a unique key and index on the MAC address column, so I immediately had to reject the CHAR(12) and likely the supporting table option. But the developers interacting with the database had varying levels of familiarity with sql and... uh... basic math, so the BIGINT was problematic. My solution was to create a pair of functions to do the conversion as painlessly as possible.

CREATE FUNCTION INTADDRESS (hexaddress CHAR(12)) RETURNS BIGINT UNSIGNED NO SQL
BEGIN
  RETURN CONV(hexaddress,16,10);
END;

CREATE FUNCTION HEXADDRESS (intaddress BIGINT UNSIGNED) RETURNS CHAR(12) NO SQL
BEGIN
  RETURN LPAD(HEX(intaddress),12,"0");
END;

All incoming MAC address strings are sent through "INTADDRESS," while all outgoing sql use "HEXADDRESS," as shown in the following examples:

INSERT INTO device_usage (mac,upload,download) VALUES(INTADDRESS('ABCDEF012456'),100,100);

SELECT SUM(upload),SUM(download) FROM device_usage WHERE mac=INTADDRESS('ABCDEF012456');

SELECT HEXADDRESS(mac),SUM(upload),SUM(download) FROM device_usage GROUP BY mac

The important thing to remember with this setup, is that "HEXADDRESS" is only for formatting the returning SQL results. In WHERE clauses, you always want to run the "INTADDRESS" function on the static side of the data being compared (the MAC address you are trying to match) rather than the database table side (which would run the function on every single row and thereby not utilize any of the relevant table indexes).