-
BIT[(M
)]
A bit-field type. M
indicates the
number of bits per value, from 1 to 64. The default is 1 if
M
is omitted.
-
TINYINT[(M
)] [UNSIGNED]
[ZEROFILL]
A very small integer. The signed range is
-128
to 127
. The
unsigned range is 0
to
255
.
-
BOOL
, BOOLEAN
These types are synonyms for TINYINT(1)
.
A value of zero is considered false. Non-zero values are
considered true.
In the future, full boolean type handling will be introduced
in accordance with standard SQL.
-
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
.
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 can handle BIGINT
in the
following cases:
When using integers to store large unsigned values
in a BIGINT
column.
In
MIN(col_name
)
or
MAX(col_name
)
,
where 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 decimal
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.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 decimal
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.
FLOAT(p
)
syntax is provided for ODBC compatibility.
-
DECIMAL[(M
[,D
])]
[UNSIGNED] [ZEROFILL]
A packed “exact” fixed-point number.
M
is the total number of decimal
digits (the precision) and D
is
the number of digits after the decimal point (the scale).
The decimal point and (for negative numbers) the
‘-
’ sign are not counted in
M
. If
D
is 0, values have no decimal
point or fractional part. The maximum number of digits
(M
) for
DECIMAL
is 65. The maximum number of
supported decimals (D
) is 30. If
D
is omitted, the default is 0.
If M
is omitted, the default is
10.
UNSIGNED
, if specified, disallows
negative values.
All basic calculations (+, -, *, /
) with
DECIMAL
columns are done with a precision
of 65 digits.
-
DEC[(M
[,D
])]
[UNSIGNED] [ZEROFILL]
,
NUMERIC[(M
[,D
])]
[UNSIGNED] [ZEROFILL]
,
FIXED[(M
[,D
])]
[UNSIGNED] [ZEROFILL]
These types are synonyms for DECIMAL
. The
FIXED
synonym is available for
compatibility with other database systems.