List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 14 2009 7:03pm
Subject:RE: MAC address as primary key - BIGINT or CHAR(12)
View as plain text  
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)
* @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."<BR>";
	//echo "startIP_long = ".$startIP_long." and endIP_long =
".$endIP_long." difference = ".$difference."<BR>";
	//echo "startIP_long = ".long2ip($startIP_long)." and endIP_long =
".long2ip($endIP_long)."<BR>";

	$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}$/",$dottedQuadIP) != 1 )
return false;
	$quad = explode(".",$dottedQuadIP);
	//echo "base: "; print_r($quad); echo "<BR>";
	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."<BR>";

	//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 "<BR>";
	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 <adam.randall@stripped>
 */
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@stripped] 
> Sent: Thursday, May 14, 2009 6:26 AM
> To: mysql@stripped
> 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
> 
>  
> 
> 

Thread
MAC address as primary key - BIGINT or CHAR(12)Ilia KATZ14 May
  • Re: MAC address as primary key - BIGINT or CHAR(12)Fish Kungfu14 May
    • Re: MAC address as primary key - BIGINT or CHAR(12)kabel14 May
    • RE: MAC address as primary key - BIGINT or CHAR(12)Ilia KATZ14 May
  • RE: MAC address as primary key - BIGINT or CHAR(12)Jerry Schwartz14 May
  • Re: MAC address as primary key - BIGINT or CHAR(12)Thomas Spahni14 May
  • Re: MAC address as primary key - BIGINT or CHAR(12)Jim Lyons14 May
  • RE: MAC address as primary key - BIGINT or CHAR(12)Daevid Vincent14 May
Re: MAC address as primary key - BIGINT or CHAR(12)Pete Wilson14 May
  • Re: MAC address as primary key - BIGINT or CHAR(12)Jim Lyons14 May
Re: MAC address as primary key - BIGINT or CHAR(12)Pete Wilson14 May
RE: MAC address as primary key - BIGINT or CHAR(12)Gavin Towey15 May
Re: MAC address as primary key - BIGINT or CHAR(12)Gavin Towey15 May