Databases Reference
In-Depth Information
For example, a
DATETIME
and a
TIMESTAMP
column can store the same kind of data: date
and time, to a precision of one second. However,
TIMESTAMP
uses only half as much
storage space, is time zone-aware, and has special autoupdating capabilities. On the
other hand, it has a much smaller range of allowable values, and sometimes its special
capabilities can be a handicap.
We discuss base data types here. MySQL supports many aliases for compatibility, such
as
INTEGER
,
BOOL
, and
NUMERIC
. These are only aliases. They can be confusing, but they
don't affect performance. If you create a table with an aliased data type and then ex-
amine
SHOW CREATE TABLE
, you'll see that MySQL reports the base type, not the alias
you used.
Whole Numbers
There are two kinds of numbers: whole numbers and real numbers (numbers with a
fractional part). If you're storing whole numbers, use one of the integer types:
TINYINT
,
SMALLINT
,
MEDIUMINT
,
INT
, or
BIGINT
. These require 8, 16, 24, 32, and 64 bits of storage
space, respectively. They can store values from −2
(
N
-1)
to 2
(
N
-1)
-1, where
N
is the number
of bits of storage space they use.
Integer types can optionally have the
UNSIGNED
attribute, which disallows negative val-
ues and approximately doubles the upper limit of positive values you can store. For
example, a
TINYINT UNSIGNED
can store values ranging from 0 to 255 instead of from
−128 to 127.
Signed and unsigned types use the same amount of storage space and have the same
performance, so use whatever's best for your data range.
Your choice determines how MySQL
stores
the data, in memory and on disk. However,
integer
computations
generally use 64-bit
BIGINT
integers, even on 32-bit architectures.
(The exceptions are some aggregate functions, which use
DECIMAL
or
DOUBLE
to perform
computations.)
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 the command-line client)
will reserve for display purposes. For storage and computational purposes,
INT(1)
is
identical to
INT(20)
.
Third-party storage engines, such as Infobright, sometimes have their
own storage formats and compression schemes, and don't necessarily
use those that are common to MySQL's built-in storage engines.
