Database Reference
In-Depth Information
Caution
When Numeric Values Are Not Numeric Values You might think that phone numbers
and Zip Codes should be stored in numeric fields (after all, they store only numeric
data), but doing so would not be advisable. If you store the Zip Code 01234 in a numer-
ic field, the number 1234 would be saved. You'd actually lose a digit.
The basic rule to follow is: If the number is a number used in calculations (sums, aver-
ages, and so on), it belongs in a numeric datatype column. If it is used as a literal string
(that happens to contain only digits), it belongs in a string datatype column.
Numeric datatypes store numbers. MariaDB supports several numeric datatypes,
each with a different range of numbers that can be stored in it. Obviously, the
larger the supported range, the more storage space needed. In addition, some
numeric datatypes support the use of decimal points (and fractional numbers),
whereas others support only whole numbers. Table D.2 lists the frequently used
MariaDB numeric datatypes.
Note
Signed Or
UNSIGNED
? All numeric datatypes (with the exception of
BIT
and
BOOLEAN
) can be signed or unsigned. Signed numeric columns can store both positive
and negative numbers; unsigned numeric columns store only positive numbers. Signed
is the default, but if you know that you'll not need to store negative values you can use
the
UNSIGNED
keyword. Doing so allows you to store values twice as large.
Table D.2
Numeric Datatypes
Datatype
Description
BIT
A bit-field, from
1
to
64
bits wide
BIGINT
Integer value, supports numbers from
-9223372036854775808
to
9223372036854775807
(or
0
to
18446744073709551615
if
UNSIGNED
)
BOOLEAN
(or
BOOL
)
Boolean flag, either
0
or
1
, used primarily for on/off flags
DECIMAL
(or
DEC
)
Floating point values with varying levels of precision
DOUBLE
Double-precision floating point values
FLOAT
Single-precision floating point values
INT
(or
INTEGER
)
Integer value, supports numbers from
-2147483648
to
2147483647
(or
0
to
4294967295
if
UNSIGNED
)
MEDIUMINT
Integer value, supports numbers from
-8388608
to
8388607
(or
0
to
16777215
if
UNSIGNED
)
REAL
4-byte floating point values
