-
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.
