Mysql and integers: INT, TINYINT and all that jazz

Today I stumbled on a discussion here where I work about what’s the best INT field in Mysql to represent boolean values and about what’s the real meaning of the *INT(number) definition.

So, I ended looking in the online Mysql manual for answer but also in the “High Performance MySQL 2nd edition” written by worldwide-fame MySQL hackers (and published by O’Really).

To resume, as Mysql Manual says: BOOLEAN is an alias for TINYINT(1), so you can use both, although BIT could be a better suited solution.

But what about the parenthesis thingie? Here there are two different opinions on the matter. According to High Performance Mysql’s authors (page 82, emphasis’ mine):

MySQL lets you specify a “width” for integer types, such as INT(11). This is meaningless for most applications: it does not restrict the legal range of values, but simply specifies the number of characters MySQL’s interactive tools (such as commandline client) will reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(20)

but now let’s look at this other page in Mysql Manual (emphasis mine):

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.

Now…who’s right? I use to trust in the Percona & OpenQuery crew but anyway the official Mysql Manual seems pretty clear about some cases in which the INT(x) value is important.
Any idea?

Advertisement