A summary of the numeric data types follows. For additional information about properties of the numeric types, see Section 10.2, “Numeric Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”.
M
indicates the maximum display width
for integer types. The maximum legal display width is 255.
Display width is unrelated to the range of values a type can
contain, as described in Section 10.2, “Numeric Types”. For
floating-point and fixed-point types,
M
is the total number of digits that
can be stored.
If you specify ZEROFILL
for a numeric column,
MySQL automatically adds the UNSIGNED
attribute to the column.
Numeric data types that allow the UNSIGNED
attribute also allow SIGNED
. However, these
data types are signed by default, so the
SIGNED
attribute has no effect.
As of MySQL 4.1, SERIAL
is an alias for
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
UNIQUE
.
SERIAL DEFAULT VALUE
in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE
.
When you use subtraction between integer values where one is
of type UNSIGNED
, the result is unsigned
unless the
NO_UNSIGNED_SUBTRACTION
SQL
mode is enabled. See Section 11.9, “Cast Functions and Operators”.
In versions of MySQL up to and lincluding 4.1,
BIT
is a synonym for
TINYINT(1)
.
TINYINT[(
M
)] [UNSIGNED]
[ZEROFILL]
A very small integer. The signed range is
-128
to 127
. The
unsigned range is 0
to
255
.
These types are synonyms for TINYINT(1)
.
The synonym BOOLEAN
was added
in MySQL 4.1.0. A value of zero is considered false. Nonzero
values are considered true:
mysql>SELECT IF(0, 'true', 'false');
+------------------------+ | IF(0, 'true', 'false') | +------------------------+ | false | +------------------------+ mysql>SELECT IF(1, 'true', 'false');
+------------------------+ | IF(1, 'true', 'false') | +------------------------+ | true | +------------------------+ mysql>SELECT IF(2, 'true', 'false');
+------------------------+ | IF(2, 'true', 'false') | +------------------------+ | true | +------------------------+
However, the values TRUE
and
FALSE
are merely aliases for
1
and 0
, respectively,
as shown here:
mysql>SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+ | IF(0 = FALSE, 'true', 'false') | +--------------------------------+ | true | +--------------------------------+ mysql>SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+ | IF(1 = TRUE, 'true', 'false') | +-------------------------------+ | true | +-------------------------------+ mysql>SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+ | IF(2 = TRUE, 'true', 'false') | +-------------------------------+ | false | +-------------------------------+ mysql>SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+ | IF(2 = FALSE, 'true', 'false') | +--------------------------------+ | false | +--------------------------------+
The last two statements display the results shown because
2
is equal to neither
1
nor 0
.
We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.
SMALLINT[(
M
)] [UNSIGNED]
[ZEROFILL]
A small integer. The signed range is
-32768
to 32767
. The
unsigned range is 0
to
65535
.
MEDIUMINT[(
M
)]
[UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is
-8388608
to 8388607
.
The unsigned range is 0
to
16777215
.
INT[(
M
)] [UNSIGNED]
[ZEROFILL]
A normal-size integer. The signed range is
-2147483648
to
2147483647
. The unsigned range is
0
to 4294967295
.
INTEGER[(
M
)] [UNSIGNED]
[ZEROFILL]
This type is a synonym for
INT
.
BIGINT[(
M
)] [UNSIGNED]
[ZEROFILL]
A large integer. The signed range is
-9223372036854775808
to
9223372036854775807
. The unsigned range
is 0
to
18446744073709551615
.
As of MySQL 4.1, SERIAL
is an alias for
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
UNIQUE
.
Some things you should be aware of with respect to
BIGINT
columns:
All arithmetic is done using signed
BIGINT
or
DOUBLE
values, so you
should not use unsigned big integers larger than
9223372036854775807
(63 bits) except
with bit functions! If you do that, some of the last
digits in the result may be wrong because of rounding
errors when converting a
BIGINT
value to a
DOUBLE
.
MySQL 4.0 can handle
BIGINT
in the following
cases:
When using integers to store large unsigned values
in a BIGINT
column.
In
MIN(
or
col_name
)MAX(
,
where col_name
)col_name
refers to
a BIGINT
column.
When using operators
(+
,
-
,
*
,
and so on) where both operands are integers.
You can always store an exact integer value in a
BIGINT
column by storing
it using a string. In this case, MySQL performs a
string-to-number conversion that involves no
intermediate double-precision representation.
The -
,
+
, and
*
operators use BIGINT
arithmetic when both operands are integer values. This
means that if you multiply two big integers (or results
from functions that return integers), you may get
unexpected results when the result is larger than
9223372036854775807
.
FLOAT[(
M
,D
)]
[UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Allowable
values are -3.402823466E+38
to
-1.175494351E-38
, 0
,
and 1.175494351E-38
to
3.402823466E+38
. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M
is the total number of digits
and D
is the number of digits
following the decimal point. If M
and D
are omitted, values are
stored to the limits allowed by the hardware. A
single-precision floating-point number is accurate to
approximately 7 decimal places.
UNSIGNED
, if specified, disallows
negative values.
Using FLOAT
might give you
some unexpected problems because all calculations in MySQL
are done with double precision. See
Section A.5.5.7, “Solving Problems with No Matching Rows”.
DOUBLE[(
M
,D
)]
[UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number.
Allowable values are
-1.7976931348623157E+308
to
-2.2250738585072014E-308
,
0
, and
2.2250738585072014E-308
to
1.7976931348623157E+308
. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M
is the total number of digits
and D
is the number of digits
following the decimal point. If M
and D
are omitted, values are
stored to the limits allowed by the hardware. A
double-precision floating-point number is accurate to
approximately 15 decimal places.
UNSIGNED
, if specified, disallows
negative values.
DOUBLE
PRECISION[(
,
M
,D
)]
[UNSIGNED] [ZEROFILL]REAL[(
M
,D
)]
[UNSIGNED] [ZEROFILL]
These types are synonyms for
DOUBLE
. Exception: If the
REAL_AS_FLOAT
SQL mode is
enabled, REAL
is a synonym
for FLOAT
rather than
DOUBLE
.
FLOAT(
p
) [UNSIGNED]
[ZEROFILL]
A floating-point number. p
represents the precision in bits, but MySQL uses this value
only to determine whether to use
FLOAT
or
DOUBLE
for the resulting data
type. If p
is from 0 to 24, the
data type becomes FLOAT
with
no M
or
D
values. If
p
is from 25 to 53, the data type
becomes DOUBLE
with no
M
or D
values. The range of the resulting column is the same as for
the single-precision FLOAT
or
double-precision DOUBLE
data
types described earlier in this section.
As of MySQL 3.23, this data type holds true floating-point
values. In earlier MySQL versions,
FLOAT(
always has two decimals.
p
)
DECIMAL[(
M
[,D
])]
[UNSIGNED] [ZEROFILL]
An unpacked fixed-point number. Behaves like a
CHAR
column;
“unpacked” means the number is stored as a
string, using one character for each digit of the value.
M
is the total number of digits
and D
is the number of digits
after the decimal point. The decimal point and (for negative
numbers) the “-
” sign are
not counted in M
, although space
for them is reserved. If D
is 0,
values have no decimal point or fractional part. The maximum
range of DECIMAL
values is
the same as for DOUBLE
, but
the actual range for a given
DECIMAL
column may be
constrained by the choice of M
and D
. If
D
is omitted, the default is 0.
If M
is omitted, the default is
10.
UNSIGNED
, if specified, disallows
negative values.
Before MySQL 3.23, the value of
M
must be large enough to
include the space needed for the sign and the decimal
point characters.
DEC[(
,
M
[,D
])]
[UNSIGNED] [ZEROFILL]NUMERIC[(
,
M
[,D
])]
[UNSIGNED] [ZEROFILL]FIXED[(
M
[,D
])]
[UNSIGNED] [ZEROFILL]
These types are synonyms for
DECIMAL
. The
FIXED
synonym was added in
MySQL 4.1.0 for compatibility with other database systems.
User Comments
Add your own comment.