[thelist] Query help?

Joshua Olson joshua at waetech.com
Mon Feb 24 07:37:01 CST 2003


----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Sunday, February 23, 2003 4:18 PM


> > Also, for MySQL, what is the limit on SMALLINT?  If it's not
> > 8 bit (TINYINT) and not 16 bit (INTEGER), then what is it?
>
> actually, mysql's tinyint is 4 bits, and smallint is 8 bits

rudy,

Usually you are spot on with everything you say.  With 4 bits, the highest
number you can represent is 15.  With 8 bits the highest number you can
represent is 255.

http://www.mysql.com/doc/en/Column_types.html

Based on that reference I've determined the following:

TINYINT is 8 bit
SMALLINT is 16 bit
MEDIUMINT is 24 bit
INTEGER is 32 bit
BIGINT is 63 bit

That is what I expected with the exception that BIGINT appears to be shy one
bit.  I don't know why.

But, INTEGER would allow for an unsigned value of at most 4294967295.  This
is much more than most applications.  SMALLINT caps out at 65535 for
unsigned values.  That is probably more reasonable for some websites.  The
MEDIUMINT, as far as I know, doesn't enjoy wide support and I therefore
wouldn't use it on databases that might need to be ported.

FYI I did some reasearch on identity gap and it appears to be a Sybase
issue:

http://www.sypron.nl/idgaps.html

>
> > so, why not use an INTEGER anyway?
>
> because smallint is ansi standard and better suited to smaller numbers?

Is INT or INTEGER not ANSI standard.  I found this reference:

http://sqlx.org/SQLXdocs/20001102-am00-sqltypes.html#integer

Interestingly, it quotes the standard as saying that SMALLINT is not
specifically defined in the standard other than it must be smaller that
INTEGER.

I tried to follow the link to see the "official" SQL99 documentation but it
said something about having to pay money and I turned right around.

Does anybody have access to a free version of the official SQL/92 or SQL/99
documentation?

-joshua




More information about the thelist mailing list