MySQL: Best way to store MAC addresses?

Question

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?

It's for an inventory system. I suppose my question is "what do most people do?". — Nick, Dec 23 '10 at 00:03

score 32 · Answer 1 · answered Dec 22 '10 at 23:44

32

use bigint unsigned (8 bytes) then you can:

select hex(mac_addr) from log;

and

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

answered Dec 22 '10 at 23:44

Jon Black

16,223
5
43
42

so you can also put Infiniband GUIDs :-) – Andre Holzner Sep 17 '15 at 07:29
1

In this example, the hex() function would return : CF15698AD, which is not a mac address. When you run __select hex(mac_addr)__, the output is not always in 12 characters as you would expect. – Mathieu Châteauvert Feb 15 '19 at 15:41
@jon black MAC addresses, for Ethernet devices anyway. are six bytes in length. Should unsigned INT(6) work? Is unsigned BIGINT(8) being suggested instead of unsigned INT(6) solely to support infiniband GUIDs? – Billy left SE for Codidact Dec 15 '20 at 04:39

score 2 · Answer 2 · answered Dec 22 '10 at 23:17

2

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.

answered Dec 22 '10 at 23:17

Christian

1,499
2
12
28

2

First time I've seen mention of PostgreSQL macaddr type – David Poxon Dec 31 '15 at 00:40
careful: PostgreSQL macaddr type does not support 8-byte MACs – Tilo Oct 10 '16 at 16:46
PostgreSQL support 8-bytes MACs type macaddr8 since version 10. https://www.postgresql.org/docs/current/datatype-net-types.html#DATATYPE-MACADDR8 – jcamdr Feb 15 '22 at 07:21

score 1 · Answer 3 · answered Dec 22 '10 at 23:23

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.

MySQL: Best way to store MAC addresses?

3 Answers3

Linked