NYCPHP Meetup

[Mark Armendariz]

On 7/28/07, PaulCheung <paulcheung at tiscali.co.uk> wrote:

> When I set the table up I set the LICENCE to INT(8). When populating this
> field I was expecting any number larger than 8 digits to be truncated and
> any number less than 8 digits to be padded out with zeros, in both cases
> this is not happening. LICENCE is being populated with a random number of
> varying size returned from the random number generator. sometimes 9 digits,
> 8 digit, 7 digits and so on; but each time a unique number. I thought INT(8)
> meant an interger of 8 digits long, no more and no less. Does anybody know a
> way around this?
>

Hi Paul,

While researching something completely unrelated (general field
validation stuff), I ran across this, which seems to be exactly what
you're looking for:

http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html
http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

(across versions)

Truncating...
"The display width does not constrain the range of values that can be
stored in the column, nor the number of digits that are displayed for
values having a width exceeding that specified for the column. For
example, a column specified as SMALLINT(3) has the usual SMALLINT
range of -32768 to 32767, and values outside the range allowed by
three characters are displayed using more than three characters."

Number Padding:
"When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is replaced with zeros. For
example, for a column declared as INT(5) ZEROFILL, a value of 4 is
retrieved as 00004. Note that if you store larger values than the
display width in an integer column, you may experience problems when
MySQL generates temporary tables for some complicated joins, because
in these cases MySQL assumes that the data fits into the original
column width."

(digression) Which confuses me as all this time i figured an unsigned
TINYINT(1) was a good boolean field and find it rather disappointing
that it makes no difference.  As far as I can tell, the length hint is
specifically for display purposes, which seems... well, useless -
considering I can't recall the last time I used SQL to show data
anywhere outside of a console window and could care less about the
column widths there. (/digression)


Mark Armendariz