Database Reference
In-Depth Information
will specify the number stored padded with up to 10 leading spaces. So if you were to store
the number 42 in an INT(10) datatype, the following would be returned:
••••••••42
Each • in the above represents a space. This is useful when you are outputting your num-
bers in columns, as it will make all of the column line up neatly (unless the number is big-
ger in this case than 999 999 999). A similar keyword, ZEROFILL when appended to the end
of the integer declaration will make the returned number be padded out with zeros instead
of spaces, so this time, the type declaration:
columnname INT(10) ZEROFILL
will produce the following when 42 is retrieved from it:
0000000042
These two variations of the datatype do not actually alter the number that is stored
within the table; it just changes the formatting of the number when it is retrieved.
UNSIGNED
You will remember from Table 3.1 that the integer datatypes ranged from a negative num-
ber to a positive one. If you use the keyword UNSIGNED after declaring an integer, the col-
umn defined will only store numbers as positive integers. The range then goes from zero
through to double the values in the To column of Table 3.1. For instance, in the following
declaration:
columnname BIGINT UNSIGNED
numbers stored can range from 0 to 18 trillion. This is fine for using integers as unique IDs
for rows, but if you start doing arithmetic with signed and unsigned datatypes it can lead
to problems. Consult the online MySQL documentation to find out more about this behav-
iour.
BOOL
A Boolean value is one that can have two states, true or false, on or off, 0 or 1. Because it is
such a simple definition, you only need to use one bit to store it, as a single bit can be either
on or off.
In MySQL, you might expect BOOL to correspond to a Boolean value, that is a value of 0
or 1 (on or off.) Although BOOL can be used to store these values, it actually is the same as
using the TINYINT(1) datatype. If you store a number bigger than 1 in a BOOL, then that
number is stored as long as it is not larger than 127. If it is bigger than 127, then 127 is
stored. If you are expecting this datatype to be a pure Boolean, it is not. If you refer back to
Ta b l e 3.1 you will see that TINYINT takes up one byte to store. A byte is 8 bits, so using
TINYINT takes eight times more storage than if BOOL was implemented correctly.
Search WWH ::
Custom Search