MySQL: Best way to store MAC addresses?

What'开发者_如何学运维s the best field type to use to store MAC addresses in a MySQL database? Also, should it be stored with a certain separator (colon or dash) or should it be stored without a separator?

use bigint unsigned (8 bytes) then you can:

select hex(mac_addr) from log;

and

insert into log (mac_addr) values (x'000CF15698AD');

Take a look here. Generally, CHAR(12) is good but details depend on your needs. Or just use PostgreSQL, which has a built-in macaddr type.

Given that MySQL does not support user defined extensions, nor does it seem to support arbitrary extensions or plugins (just for storage engines), your best bet is to store it as a CHAR(17) as an ASCII string in standard notation (e.g., with colon separators), or a small BLOB and store the bytes directly. However, the string notation is going to be more friendly for most applications.

You may want to pair it with a trigger that validates that it is a MAC address, since that is really the only way to enforce data validity without support for custom types.