MAC address as primary key - BIGINT or CHAR(12)

------_=_NextPart_001_01C9D497.8DCCC042
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi.
Currently I have a table:
1. MAC address defined as BIGINT
2. MAC address set as primary key
=20
Should I consider changing it to CHAR(12)?
=20
Replies will be appreciated.
Ilia

=20


------_=_NextPart_001_01C9D497.8DCCC042--

Re: MAC address as primary key - BIGINT or CHAR(12)

--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" 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

--001636e90cddd7f9c70469dfa8fe--

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" 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=gcdmg-mysql-2@m.gmane.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:ikatz@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=gcdmg-mysql-2@m.gmane.org

RE: MAC address as primary key - BIGINT or CHAR(12)

------_=_NextPart_001_01C9D49D.D5231FE7
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.

=20

for example: 00:1D:7D:48:08:8F=20

=20

pair value =20

00 0 1st byte

1D 29 2nd byte

7D 125 3rd byte

48 72 4th byte

08 8 5th byte

8F 143 6th byte

=20

the last 2 bytes (of the BIGINT) left unused.

=20

Ilia

________________________________

From: Fish Kungfu [mailto:fish.kungfu@gmail.com]=20
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)

=20

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"
wrote:
=09
Hi.
Currently I have a table:
1. MAC address defined as BIGINT
2. MAC address set as primary key
=09
Should I consider changing it to CHAR(12)?
=09
Replies will be appreciated.
Ilia
=09
=09
=09




************************************************************ ************
************
This footnote confirms that this email message has been scanned by
PineApp Mail-SeCure for the presence of malicious code, vandals &
computer viruses.
************************************************************ ************
************


------_=_NextPart_001_01C9D49D.D5231FE7--

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=gcdmg-mysql-2@m.gmane.org

Re: MAC address as primary key - BIGINT or CHAR(12)

--0016e64763e0f331000469e11d8e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 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

--0016e64763e0f331000469e11d8e--

Re: MAC address as primary key - BIGINT or CHAR(12)

=0AI'm new to MySQL so can't answer the OP's question, but: MAC addres=
ses do not by design contain letters. Native MAC addresses are 48-bit (6-by=
te) integers: http://standards.ieee.org/getieee802/download/802-2001=
..pdf The confusion arises because a MAC address is usually /represente=
d/ as hexadecimal, and that might contain letters, but MAC addresses nativ=
ely are pure 6-byte integers. So the issue is whether you want to ind=
ex 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 answ=
er, though, from someone with experience. -- Pete Wilson =0A http:=
//www.pwilson.net/ =0A--- On Thu, 5/14/09, Jim Lyons ..com> wrote: > From: Jim Lyons =0A> Subject: Re:=
MAC address as primary key - BIGINT or CHAR(12)=0A> To: "Ilia KATZ" @dane-elec.co.il>=0A> Cc: mysql@lists.mysql.com=0A> Date: Thursday, May 14,=
2009, 11:38 AM=0A> Definitely CHAR (or VARCHAR).=0A> =0A> If the format of=
a MAC address changes at all, you could be=0A> in real=0A> trouble.=A0 Als=
o, if a MAC address can have a leading 0=0A> (I don't know anything=0A> abo=
ut MAC addresses), then storing it as some sort of=0A> number could lose=0A=
> that.=0A> =0A> This is a general rule for me.=A0 A field might only=0A> c=
ontain numbers (at one=0A> particular point in time)=A0 but if those number=
s are=0A> really nominal data (in=0A> which the size or order does not matt=
er) then they should=0A> be CHAR or VARCHAR=0A> fields anyway.=0A> =0A> On =
Thu, May 14, 2009 at 8:26 AM, Ilia KATZ =0A> wrote:=
=0A> =0A> > Hi.=0A> > Currently I have a table:=0A> > 1. MAC address define=
d as BIGINT=0A> > 2. MAC address set as primary key=0A> >=0A> > Should I co=
nsider changing it to CHAR(12)?=0A> >=0A> > Replies will be appreciated.=0A=
> > Ilia=0A> >=0A> >=0A> >=0A> >=0A> =0A> =0A> -- =0A> Jim Lyons=0A> Web de=
veloper / Database administrator=0A> http://www.weblyons.com=0A> =0A =


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MAC address as primary key - BIGINT or CHAR(12)

--0016e6471b0a96f8d20469e19437
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 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 wrote:
>
> > From: Jim Lyons
> > Subject: Re: MAC address as primary key - BIGINT or CHAR(12)
> > To: "Ilia KATZ"
> > 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
> > 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

--0016e6471b0a96f8d20469e19437--

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-shoot=
ing, 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, p=
er row.) It all depends, but in general, would you call that too costl=
y, given the benefit? -- Pete Wilson =0A http://www.pwilson.net/=
=0A--- On Thu, 5/14/09, Jim Lyons wrote: =
> From: Jim Lyons > As I said in my post, this =
is a=0A> general principle for me.=A0 Nominal data should have a data=0A> t=
ype of some sort of character.=A0 You will never run into=0A> unexpected=A0=
problems=A0 down the line. =0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: MAC address as primary key - BIGINT or CHAR(12)

------=_NextPart_000_08EC_01C9D48C.004E9630
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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-functio ns.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<
-----------------------------------------------
/**
* 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)
* @since 3.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).
* @since 3.0
* @author Daevid Vincent [daevid@]
* @date 10.13.03
*/
function deduceRange($iprange)
{
//check if we're in / notation (CIDR)
if ( strstr($iprange, "/") )
{
list($myIP,$mySlash) = preg_split("/\//",$iprange, -1,
PREG_SPLIT_NO_EMPTY);
return CalculateSubnetIPs(trim($myIP),trim($mySlash));
}
//check if we are using a human readable range
elseif ( strstr($iprange, ":") )
{
return CalculateIPRange($iprange);
}
//otherwise just return what they put in as it's probably a single
IP or FQDN
else return array($iprange); //single ip or name (no / or : found)
} //deduceRange()

/**
* 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 : NNN.NNN.NNN.NNN (spaces are
okay).
* @since 3.0
* @author Daevid Vincent [daevid@]
* @date 10.13.03
*/
function CalculateIPRange($iprange)
{
$temp = preg_split("/:/",$iprange, -1, PREG_SPLIT_NO_EMPTY);
$QRange1 = $temp[0];
$QRange2 = $temp[1];

if ($QRange2 == "") return array($iprange); //special case, they
didn't put a second quad parameter

//basic error handling to see if it is generally a valid IP in the
form N.N.N.N
if ( preg_match("/\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/",$QRange1) !=
1 ) return array(-1);
if ( preg_match("/\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/",$QRange2) !=
1 ) return array(-1);

$quad1 = explode(".",$QRange1);
foreach($quad1 as $key => $val)
{
$quad1[$key] = intval($val);
if ($quad1[$key] < 0 || $quad1[$key] > 255) return
array(-2);
}

$quad2 = explode(".",$QRange2);
foreach($quad2 as $key => $val)
{
$quad2[$key] = intval($val);
if ($quad2[$key] < 0 || $quad2[$key] > 255) return
array(-2);
}

$startIP_long = ip2long($QRange1);
$endIP_long = ip2long($QRange2);

if ($endIP_long < $startIP_long)
{
$switcharoo = $startIP_long;
$startIP_long = $endIP_long;
$endIP_long = $switcharoo;
}
//$difference = $endIP_long - $startIP_long;
//echo "QRange1 = ".$QRange1." and QRange2 = ".$QRange2."
";
//echo "startIP_long = ".$startIP_long." and endIP_long =
".$endIP_long." difference = ".$difference."
";
//echo "startIP_long = ".long2ip($startIP_long)." and endIP_long =
".long2ip($endIP_long)."
";

$ip = array();
$k = 0;
for ($i = $startIP_long ; $i <= $endIP_long; $i++)
{
//[dv] because Cisco decided to embrace extend, and Tony is
a whiner,
// we no longer check to make sure that a value is
not .0 or .255
/*
$temp = long2ip($i);
$thisQuad = explode(".",$temp); //this is a total hack.
there must be a better way.
if ($thisQuad[3] > 0 && $thisQuad[3] < 255)
$ip[$k++] = $temp;
*/
$ip[$k++] = long2ip($i);
}

return $ip;
} //CalculateIPRange()


/**
* basic error handling to see if it is generally a valid IP in the form
N.N.N.N
*
* @access public
* @return boolean
* @param $dottedQuadIP IP Address in the form NNN.NNN.NNN.NNN
* @since 3.0
* @author Daevid Vincent [daevid@]
* @date 10.13.03
*/
function isDottedQuad($dottedQuadIP)
{
// trimming should happen pre function. This is because if we trim
in here and it validates, but we don't trim outside, it may screw up things
post function (like adding to db, or other functions)
//$dottedQuadIP = trim($dottedQuadIP);
if (
preg_match("/^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$/",$dottedQ uadIP) != 1 )
return false;
$quad = explode(".",$dottedQuadIP);
//echo "base: "; print_r($quad); echo "
";
foreach($quad as $key => $val)
{
$quad[$key] = intval($val);
if ($quad[$key] < 0 || $quad[$key] > 255) return false;
}

return true;
}

/**
* This function will return an array of either a negative error code or all
possible IP addresses in the given /subnet.
*
* @access public
* @return array
* @param $iprange IP Address in the form NNN.NNN.NNN.NNN
* @param $slash the CIDR slash notation /1-/32
* @param $extendedInfo boolean to toggle netmask, broadcast, network,
IP
* @since 3.0
* @author Daevid Vincent [daevid@]
* @date 10.13.03
*/
function CalculateSubnetIPs($iprange,$slash,$extendedInfo = false)
{
if ($slash == "" || !is_numeric($slash)) return array($iprange);
//special case

//echo "slash: /".$slash."
";

//basic error handling to see if it is generally a valid IP in the
form N.N.N.N
if ( !isDottedQuad($iprange) ) return array(-1);
$quad = explode(".",$iprange);
$baseIP = $quad[0].".".$quad[1].".".$quad[2];
//echo "base: "; print_r($quad); echo "
";
foreach($quad as $key => $val)
{
$quad[$key] = intval($val);
if ($quad[$key] < 0 || $quad[$key] > 255) return array(-2);
}

if ($slash == 32 || $slash == 0) return array($iprange); //special
case
if ($slash == 31) return array(-3); //no useable IPs in this range.
if ($slash < 1 || $slash > 32) return array(-4);

$netmask = 0;
for( $i = 0; $i < 32; $i++ )
{
$netmask = $netmask << 1;
$netmask |= (($i < $slash) ? 1 : 0);
}
$network = ip2long($iprange) & $netmask;
$broadcast = ip2long($iprange) | (~$netmask);
$naddrs = pow(2,(32 - $slash)) - 1;
$i = 0;
// iterate over the set IP addresses in the range
// and convert them from a long value to the dotted-quad value
for($ipaddr = $network; $ipaddr <= $network + $naddrs ; $ipaddr++)
//[dv] commented out for [CR3418] so we can allow these .0 and .255
ends for Cisco folks
//for($ipaddr = $network + 1; $ipaddr < $network + $naddrs ;
$ipaddr++)
{
$ip[$i++] = long2ip($ipaddr);
}


if ($extendedInfo == true)
return array( 'netmask' => $netmask,
'network' => $network,
'broadcast' => $broadcast,
'ip' => $ip );
else
return $ip; //if no extended info requested, then just
return the ip range as an array.
} //CalculateSubnetIPs()

/**
* determines if two subnets overlap
*
* @access public
* @return boolean
* @param $sub1 subnet 1
* @param $sub2 subnet 2
* @since 3.0
*/
function subnet_overlap($sub1, $sub2){
list($low1, $high1) = find_subnet_limits($sub1);
list($low2, $high2) = find_subnet_limits($sub2);
return !(($high1 < $low2) || ($high2 < $low1));
}

/**
* this function returns a 2-element array that contains the lowest and the
highest IP for a given subnet
*
* the first part is always an IP address. The second can be either another
* IP address (in this case we use ':' to separate the two, and the resulting
* range is bound by the two IP addresses) or a netmask (in which case we
* use '/' and actually have to do some trivial binary math)
*
* @access public
* @return a 2-element array that contains the lowest and the highest
IP for a given subnet
* @param $sub subnet in the form NNN.NNN.NNN.NNN/CIDR or
NNN.NNN.NNN.NNN : NNN.NNN.NNN.NNN
* @since 3.0
*/
function find_subnet_limits($sub){
$delim = strpos($sub, '/');
if($delim !== FALSE){
// the second part is a netmask
$ip = ip2long(trim(substr($sub, 0, $delim)));
$mask_ = substr($sub, $delim+1);
$mask_ = calculate_subnet_mask($mask_);
$low = $ip & $mask_;
$high = $ip | (~$mask_);
}else{
// the second part is NOT a netmask. maybe it is an IP...
$delim = strpos($sub, ':');
if($delim !== FALSE){
// the second part is the IP
$low = ip2long(trim(substr($sub, 0, $delim)));
$high = ip2long(trim(substr($sub, $delim+1)));
// swap if the user entered addresses in reverse
if($high < $low){
$t = $high;
$high = $low;
$low = $t;
}
}else{
// the second part is who-knows-what.
return FALSE;
}
}
return array($low, $high);
}

/**
* this function calculates the subnet in quad notation from the 2-digit
notation
*
* @access public
* @return a subnet mask.
* @param $two_digit the two digit CIDR slash notation
* @since 3.0
*/
function calculate_subnet_mask($two_digit){
$mask = 0;
for($i = 0; $i < 32; $i++){
$mask <<= 1;
if($i < $two_digit) $mask |= 1;
}
return $mask;
}

/**
* Extremely cool function nabbed off php.net... http://www.php.net/network
* Give it an IP, a network 192.168.20.0 and a mask like 24
* and it will tell you if the IP is in that network range
*
* @access public
* @return boolean
* @param string $ip target ipaddress in dotted quad notation
* @param string $net network ipaddress in dotted quad notation
* @param int $mask slash notation for number of masked bits
* @since 3.0
*/
function isipin($ip,$net,$mask)
{
if ($mask < 1 or $mask > 32) return false;

$ip = ip2long($ip);
$rede = ip2long($net);
$mask = ip2long($mask);
$real_mask = ~0 << (32 - $mask);

$netmask = $rede & $real_mask;
$res = $ip & $real_mask;

return ($res == $netmask);
}

/**
* figure out the network vitals given a dotted quad and a CIDR slash
*
* @access public
* @return array with broadcast, network, netmask, total, low, high
* @param string $quad ipaddress
* @param int $slash CIDR
* @since 4.03
*/
function networkVitals($quad, $slash)
{
if ( !isDottedQuad($quad) ) return array(-1);
if (intval($slash) < 1 || intval($slash) > 32 ) return array(-2);

// create a bitmask fron network bits
$bitmask = 0;
for($i = 0; $i < 32; $i++)
{
$bitmask = $bitmask << 1;
if($i < $slash) $bitmask = $bitmask | 1;
}

return array(
'broadcast'=>long2ip(((ip2long($quad) &
$bitmask) | ~$bitmask)),
'network'=>long2ip((ip2long($quad) &
$bitmask)),
'netmask'=>long2ip($bitmask),
'total'=>~$bitmask - 1,
'low'=>long2ip((ip2long($quad) &
$bitmask)+1),
'high'=>long2ip(((ip2long($quad) & $bitmask)
| ~$bitmask)-1)
);
}

/**
* determine if name is a valid Windows logon name in the form
'\\domain\user' or 'domain\user'
*
* @access public
* @return boolean
* @param string $logon
* @since 4.1
* @date 12/12/2005
*/
function isValidWinLogon($logon)
{
$logon = trim($logon);
preg_match('/^(\\\\\\\\)?.+\\\\.+$/', $logon, $matches);
return (count($matches) > 0);
//return (strpos($logon, '\\') === false)?false:true;
}

/**
* determine if name is a valid FQDN (Fully Qualified Domain Name) in the
from 'user@domain'
*
* @access public
* @return boolean
* @param string $logon
* @since 4.1
* @date 12/12/2005
*/
function isValidFQDN($logon)
{
$logon = trim($logon);
preg_match('/.+@.+/', $logon, $matches);
return (count($matches) > 0);
}


/**
* Convert a hostname to a valid and compliant form.
*
* - Character set as set out by RFC952:
*
* 1. A "name" (Net, Host, Gateway, or Domain name) is a text string up
* to 24 characters drawn from the alphabet (A-Z), digits (0-9), minus
* sign (-), and period (.). Note that periods are only allowed when
* they serve to delimit components of "domain style names".
*
* Note that the length restriction has changed, the current suggested
* approach is 64 characters, but write your app to support 255.
*
* HOWEVER, Mysql currently only supports up to 60 characters, otherwise
* replication breaks.
*
* Note that the name is case-insensitive, and any app accepting a host
* name should convert it to lowercase as our canonical internal format
* before writing to the db or hosts file. To truly be bullet proof, you
* should always do a case blind compare when comparing host names.
*
* So here are my suggestions to fix both the GUI and lockdown-setup:
*
* - Make sure only allowed characters are used. A-z,a-z, 0-9, '-', and '.'
* - Limit host name length to 60
* - Convert to lowercase before saving
*
* @access public
* @return string
* @param string $hostname
* @since 4.5
* @date 07/18/2006
*/
function hostname_RFC952($hostname)
{
// remove extra characters and invalid characters from beginning of
hostname
$hostname = preg_replace( '/[^A-Za-z0-9\-.]/', '', $hostname );
$new_hostname = array();

// clean each label
$first = true;
foreach( explode( '.', $hostname ) as $i )
{

if( $first )
$i = preg_replace( '/^[0-9\-]+/', '', $i );

$i = preg_replace( '/^-+/', '', $i );

// only add a label when it contains data
if( strlen( $i ) > 0 )
{
$first = false;
$new_hostname[] = $i;
}
}

// restore the cleansed hostname
$hostname = implode( '.', $new_hostname );

if( strlen( $hostname ) > 60 )
$hostname = preg_replace( '/-+$/', '', substr( $hostname, 0,
60 ) );
elseif( strlen( $hostname ) == 1 )
$hostname = substr( strtolower( PRODUCT_ENF ), 0, 3 ) . '-'
.. $hostname;
elseif( strlen( $hostname ) == 0 )
$hostname = substr( strtolower( PRODUCT_ENF ), 0, 3 ) . '-'
.. $_SERVER[ 'SERVER_ADDR' ];

return( $hostname );
}

/**
* Returns true if the string a valid hostname as defined by RFC952.
* This means that the value is less than 25 characters, validates as a
domain name (A-Z, 0-9, period (.) and - characters only) and
* it does not conatain a period (yes, this is contrary to the previous
statement)
*
* @access public
* @param string $hostname
* @return boolean
* @since 4.2.6
* @date 7/11/2007
*/
function isValidHostnameRFC952( $hostname )
{
return ( isValidDomain( $hostname ) && ( preg_match( '/^\d/',
$hostname) == 0 ) && ( strlen( $hostname ) <= 60 ) );
}


/**
* Will return true if the value passed in contains only the characters A-Z,
0-9, period (.) and hyphen (-)
* Any sub part of the domain, like www, google and com cannot start or end
with a hyphen. While I believe that
* you shouldn't be allowed to have a domain starting with a numeric, that
seems to be allowed now with domains
* such as www.123.com, which means that a hostname can also contain that.
Each part of a domain can only be 63
* characters long.
*
* @param string $value The domain to test
* @return boolean
* @since 4.5.5
* @date 7/11/2007
* @access public
* @author Adam Randall
*/
function isValidDomain( $value = '' )
{
foreach( explode( '.', strtolower( $value ) ) as $i )
{
if( ( strlen( $i ) == 0 ) || ( strlen( $i ) > 63 ) || (
$i[0] === '-' ) || ( $i[ strlen( $i ) - 1 ] === '-' ) || ( preg_match(
'/^[a-z0-9\-]+$/', $i ) == 0 ) )
return( false );
}
return true;
}


/**
* Returns true if the string is a wildcard IP (e.g. 192.*, 192.168.*,
192.168.1.*).
*
* @access public
* @param string $ip
* @return boolean
* @since 4.2.6
* @date 10/05/2006
*/
function isValidWildcardIP( $ip )
{
return ( 1 == preg_match( "/((\d{1,3})\.){1,3}\*/", $ip ) );
}

/**
* Converts a wildcard IP (e.g. 192.*, 192.168.*, 192.168.1.*) to a CIDR
form.
*
* @access public
* @param string $ip
* @return string
* @since 4.2.6
* @date 10/05/2006
*/
function wildcardIP2CIDR( $ip )
{
$result = "";
if ( isValidWildcardIP( $ip ) )
{
$parts = explode( ".", $ip );
unset( $parts[ count( $parts ) - 1 ] ); // Get rid of the
asterisk.
$cidr_bits = count( $parts ) * 8;

for ( $i = 3; $i >= 0; $i-- )
{
if ( !isset( $parts[ $i ] ) )
{
$parts[ $i ] = '0';
}
else
{
break;
}
}

$temp = implode( '.', $parts );

// We should have an IP like 192.168.0.0 now. Validate it.
if ( false !== ip2long( $temp ) )
{
$result = $temp."/".$cidr_bits;
}
}

return $result;
}

/**
* Returns true if the string is a valid CIDR.
*
* @access public
* @param string $cidr
* @return boolean
* @since 4.2.6
* @date 10/05/2006
*/
function isValidCIDR( $ip )
{
$parts = explode( "/", $ip );
if ( 2 != count( $parts ) ) return false;

$cidr_bits = -1;
if ( 1 == preg_match( "/^[0-9]+$/", $parts[ 1 ] ) )
{
$temp = intval( $parts[ 1 ] );
if ( ( $temp >= 0 ) && ( $temp <= 32 ) )
{
$cidr_bits = $temp;
}
}

return ( ( $cidr_bits >= 0 ) && isDottedQuad( $parts[ 0 ] ) );
}

/**
* Returns true if the string is a valid MAC
*
* @access public
* @param string $mac
* @return boolean
*/
function isValidMAC( $mac )
{
return
preg_match("/^([0-9A-Fa-f]{2}):([0-9A-Fa-f]{2}):([0-9A-Fa-f] {2}):([0-9A-Fa-f
]{2}):([0-9A-Fa-f]{2}):([0-9A-Fa-f]{2})$/",$mac);
}

/**
* Returns true if the string is a valid NetBIOS name.
*
* @access public
* @param string $mac
* @return boolean
*/
function isValidNetBIOS( $name )
{
// See http://support.microsoft.com/kb/q188997/
return preg_match( "/^[0-9A-Za-z\!@#\$%\^&\(\)\-'\{\}\.\~]{1,15}$/",
$name );
}
?>
------------------------------------------- 8<
-----------------------------------------------

> -----Original Message-----
> From: Ilia KATZ [mailto:ikatz@dane-elec.co.il]
> Sent: Thursday, May 14, 2009 6: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
>
>
>
>


------=_NextPart_000_08EC_01C9D48C.004E9630
Content-Type: text/plain; charset=us-ascii


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
------=_NextPart_000_08EC_01C9D48C.004E9630--

RE: MAC address as primary key - BIGINT or CHAR(12)

--_000_30B3DF511CEC5C4DAE4D0D290504753413336567E0AAApmgiloca l_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

A MAC address is just a number, it doesn't contain letters unless you're do=
ing something silly like storing the HEX representation of it. Do not use C=
HAR! 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 a=
ppropriate. When you select the value you can do SELECT HEX(mac) FROM tabl=
e; to get a more readable version of it. When you're storing values you c=
an 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

From: Fish Kungfu

Cc: mysql@lists.mysql.com

Subject: Re: MAC address as primary key - BIGINT or CHAR(12)

Message-ID:



--001636e90cddd7f9c70469dfa8fe

Content-Type: text/plain; charset=3DISO-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" 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 c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--_000_30B3DF511CEC5C4DAE4D0D290504753413336567E0AAApmgiloca l_--

Re: MAC address as primary key - BIGINT or CHAR(12)

--_000_30B3DF511CEC5C4DAE4D0D290504753413336567E6AAApmgiloca l_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

"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 c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--_000_30B3DF511CEC5C4DAE4D0D290504753413336567E6AAApmgiloca l_--