Re: MAC address as primary key - BIGINT or CHAR(12)
Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia
Re: MAC address as primary key - BIGINT or CHAR(12)
On Thursday 14 May 2009 09:53:58 am Fish Kungfu wrote: Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia And, if you use default charsets of anything else, make sure you set this column to CHARACTER SET ascii. A smaller index is a happier index. kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
You might even want to plan for longer MAC addresses. I don't follow developments in that area, but they had to go from IP4 to IP6 and they might have to introduce longer MAC addresses. It isn't hard to change a MySQL field definition, but your applications would be more of a problem. -Original Message- From: Ilia KATZ [mailto:ik...@dane-elec.co.il] Sent: Thursday, May 14, 2009 9:26 AM To: mysql@lists.mysql.com Subject: MAC address as primary key - BIGINT or CHAR(12) Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
Not exactly. The allowed letters are A,B,C,D, E, F. Every 2 characters (not including separators) can be treated as a hexadecimal number that can be represented with one byte. 6 bytes in total. for example: 00:1D:7D:48:08:8F pair value 00 0 1st byte 1D29 2nd byte 7D125 3rd byte 48 72 4th byte 08 8 5th byte 8F143 6th byte the last 2 bytes (of the BIGINT) left unused. Ilia From: Fish Kungfu [mailto:fish.kun...@gmail.com] Sent: Thursday, May 14, 2009 3:54 PM To: Ilia KATZ Cc: mysql@lists.mysql.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia This footnote confirms that this email message has been scanned by PineApp Mail-SeCure for the presence of malicious code, vandals computer viruses.
Re: MAC address as primary key - BIGINT or CHAR(12)
On Thu, 14 May 2009, Ilia KATZ wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia Hi It depends. You may convert the MAC address to a decimal integer and store it as a BIGINT. Use UNSIGNED as well; there are no negative numbers involved. This may gain some speed and saves storage space. The drawback I can see is that these numbers are not human readable, but you may convert back to HEX when retrieving data. And it may break when they start using larger MAC addresses eventually. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MAC address as primary key - BIGINT or CHAR(12)
Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MAC address as primary key - BIGINT or CHAR(12)
I'm new to MySQL so can't answer the OP's question, but: MAC addresses do not by design contain letters. Native MAC addresses are 48-bit (6-byte) integers: http://standards.ieee.org/getieee802/download/802-2001.pdf The confusion arises because a MAC address is usually /represented/ as hexadecimal, and that might contain letters, but MAC addresses natively are pure 6-byte integers. So the issue is whether you want to index by a 48-bit number or a 12-character ascii string. For efficiency's sake, I'm guessing you'd choose the former. I'll be interested in the answer, though, from someone with experience. -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) To: Ilia KATZ ik...@dane-elec.co.il Cc: mysql@lists.mysql.com Date: Thursday, May 14, 2009, 11:38 AM Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MAC address as primary key - BIGINT or CHAR(12)
As I said in my post, this is a general principle for me. Nominal data should have a data type of some sort of character. You will never run into unexpected problems down the line. On Thu, May 14, 2009 at 11:04 AM, Pete Wilson pete...@yahoo.com wrote: I'm new to MySQL so can't answer the OP's question, but: MAC addresses do not by design contain letters. Native MAC addresses are 48-bit (6-byte) integers: http://standards.ieee.org/getieee802/download/802-2001.pdf The confusion arises because a MAC address is usually /represented/ as hexadecimal, and that might contain letters, but MAC addresses natively are pure 6-byte integers. So the issue is whether you want to index by a 48-bit number or a 12-character ascii string. For efficiency's sake, I'm guessing you'd choose the former. I'll be interested in the answer, though, from someone with experience. -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) To: Ilia KATZ ik...@dane-elec.co.il Cc: mysql@lists.mysql.com Date: Thursday, May 14, 2009, 11:38 AM Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MAC address as primary key - BIGINT or CHAR(12)
I agree, and didn't mean to say that I disagreed. This is certainly one of the top five principles to follow, imo. Too many times, while trouble-shooting, I've run up on the rock of a binary (meaning: indecipherable) field. What is the cost of including the binary representation (for indexing) and also the readable representation (just for debugging/problem-solving)? (Well, of course I know what the cost is: it's 12 bytes, plus overhead, per row.) It all depends, but in general, would you call that too costly, given the benefit? -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com As I said in my post, this is a general principle for me. Nominal data should have a data type of some sort of character. You will never run into unexpected problems down the line. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
Store as CHAR(12), not VARCHAR because they are ALWAYS a fixed length. It
saves space and is faster to index/lookup.
If you're using IPv4 only
then stick with CHAR(12).
elseif you think you'll go to IPv6 then
if large dataset ( 1M rows say),
plan for it now as ALTER later will take a long time
else
stick with (12) and ALTER later when needed
If you're storing IP addresses, DO NOT store them as characters. Store them
as UNSIGNED INTEGERS and use the INET_ATON() and INET_NTOA() functions.
Aside from being more efficient to store, you will get proper sorting.
Sorting strings will not work the way you want it to.
suggested reading:
http://dev.mysql.com/doc/refman/5.1/en/data-size.html
http://dev.mysql.com/doc/refman/5.0/en/char.html
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function
_inet-aton
http://us2.php.net/manual/en/function.inet-ntop.php
http://us2.php.net/manual/en/function.inet-pton.php
http://us2.php.net/manual/en/function.ip2long.php
http://us2.php.net/manual/en/function.long2ip.php
I tried to attach a file with many useful networking functions, if it didn't
go through because for some silly reason attachments are not allowed, I also
pasted it here below. You'll have to reformat because this list software I'm
sure will mangle the code. :-\
--- 8
---
?php
/**
* Given a netmask in dotted quad notation, returns the CIDR 'slash'
notation.
*
* @access public
* @return CIDR 'slash' notation
* @param$netmask the dotted quad netmask.
* @param $verify force the result to be false if the netmask is invalid
instead of returning a safe, small range (27)
* @since3.0
*/
function netmask2Range($netmask, $verify = false) {
$CIDRMap = array(
1 = 128.0.0.0,
2 = 192.0.0.0,
3 = 224.0.0.0,
4 = 240.0.0.0,
5 = 248.0.0.0,
6 = 252.0.0.0,
7 = 254.0.0.0,
8 = 255.0.0.0,
9 = 255.128.0.0,
10 = 255.192.0.0,
11 = 255.240.0.0,
12 = 255.240.0.0,
13 = 255.248.0.0,
14 = 255.252.0.0,
15 = 255.254.0.0,
16 = 255.255.0.0,
17 = 255.255.128.0,
18 = 255.255.192.0,
19 = 255.255.224.0,
20 = 255.255.240.0,
21 = 255.255.248.0,
22 = 255.255.252.0,
23 = 255.255.254.0,
24 = 255.255.255.0,
25 = 255.255.255.128,
26 = 255.255.255.192,
27 = 255.255.255.224,
28 = 255.255.255.240,
29 = 255.255.255.248,
30 = 255.255.255.252,
31 = 255.255.255.254,
32 = 255.255.255.255
);
$CIDR = array_search($netmask,$CIDRMap);
if ($CIDR == $verify === false)
$CIDR = 27; // If we can't determine the range we will
default to a small range (/27)
elseif ( $CIDR == $verify !== false )
$CIDR = false; // if verify is set to true and the result is
empty, return false instead of a safe range
return $CIDR;
} //netmask2Range
/**
* This function will return an array of either a negative error code
* or all possible IP addresses in the given range.
*
* @access public
* @return an array of either a negative error code or all possible IP
addresses in the given range
* @param$iprange NNN.NNN.NNN.NNN/CIDR or NNN.NNN.NNN.NNN :
NNN.NNN.NNN.NNN (spaces are okay).
* @since3.0
* @author Daevid Vincent [dae...@]
* @date 10.13.03
*/
function deduceRange($iprange)
{
//check if we're in / notation (CIDR)
RE: MAC address as primary key - BIGINT or CHAR(12)
A MAC address is just a number, it doesn't contain letters unless you're doing something silly like storing the HEX representation of it. Do not use CHAR! This does DOUBLE for all of you storing IP addresses! Since a MAC address is going to be between 48 and 64 bits, then BIGINT is appropriate. When you select the value you can do SELECT HEX(mac) FROM table; to get a more readable version of it. When you're storing values you can do: INSERT INTO table (mac) VALUES (x'FFEEDDCCBBAA9988'); to convert a hex string to the numeric value. Date: Thu, 14 May 2009 09:53:58 -0400 To: Ilia KATZ ik...@dane-elec.co.il From: Fish Kungfu fish.kun...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) Message-ID: f76e38f90905140653o4f6513aft103e8c3b526b3...@mail.gmail.com --001636e90cddd7f9c70469dfa8fe Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: MAC address as primary key - BIGINT or CHAR(12)
I've run up on the rock of a binary (meaning: indecipherable) field. SELECT hex(some_binary_field) FROM table; Solved. The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
