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?
在MySQL数据库中存储MAC地址的最佳字段类型是什么?同样，它应该存储在一个特定的分隔符(冒号或破折号)中，还是应该存储在没有分隔符的情况下?
3 个解决方案
#1
29
use bigint unsigned (8 bytes) then you can:
使用bigint unsigned(8字节)，然后您可以:
select hex(mac_addr) from log;
and
和
insert into log (mac_addr) values (x'000CF15698AD');
#2
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.
看看这里。一般来说，CHAR(12)是好的，但是细节取决于您的需要。或者使用PostgreSQL，它有一个内置的macaddr类型。
#3
1
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.
鉴于MySQL不支持用户定义的扩展,似乎也不支持任意扩展或插件(只是为了存储引擎),你最好将它存储为一个字符(17)作为标准符号ASCII字符串(例如,用冒号分隔符),或一个小团,直接存储字节。然而，字符串表示法对大多数应用程序来说更加友好。
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.
您可能希望将它与验证它是MAC地址的触发器进行配对，因为这实际上是在不支持自定义类型的情况下强制数据有效性的惟一方法。
#1
29
use bigint unsigned (8 bytes) then you can:
使用bigint unsigned(8字节)，然后您可以:
select hex(mac_addr) from log;
and
和
insert into log (mac_addr) values (x'000CF15698AD');
#2
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.
看看这里。一般来说，CHAR(12)是好的，但是细节取决于您的需要。或者使用PostgreSQL，它有一个内置的macaddr类型。
#3
1
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.
鉴于MySQL不支持用户定义的扩展,似乎也不支持任意扩展或插件(只是为了存储引擎),你最好将它存储为一个字符(17)作为标准符号ASCII字符串(例如,用冒号分隔符),或一个小团,直接存储字节。然而，字符串表示法对大多数应用程序来说更加友好。
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.
您可能希望将它与验证它是MAC地址的触发器进行配对，因为这实际上是在不支持自定义类型的情况下强制数据有效性的惟一方法。