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
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)
(.) and - characters only) and
* it does not conatain a period (yes, this is contrary to the previous
statement)
*
* @access public
* @paramstring $hostname
* @return boolean
* @since4.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.rand...@lockdownnetworks.com
*/
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
* @paramstring $ip
* @return boolean
* @since4.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
* @paramstring $ip
* @return string
* @since4.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
* @paramstring $cidr
* @return boolean
* @since4.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
* @paramstring $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
* @paramstring $mac
* @return boolean
*/
function isValidNetBIOS( $name )
{
// See http://support.microsoft.com/kb/q188997/
return preg_match( /^[0-9a-za-...@#\$%\^\(\)\-'\{\}\.\~]{1,15}$/,
$name );
}
?
--- 8
---
-Original Message-
From: Ilia KATZ [mailto:ik...@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
--
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)
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.
