Numeric Type Overview
A summary of the numeric data types follows. For additional information about properties and storage requirements of the numeric types, see , "Numeric Types", and , "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 , "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, MariaDB automatically adds the UNSIGNED
attribute to the column.
Numeric data types that permit the UNSIGNED
attribute also permit SIGNED
. However, these data types are signed by default, so the SIGNED
attribute has no effect.
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
.Warning
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 , "Cast Functions and Operators".
BIT[(
M
)]A bit-field type.
M
indicates the number of bits per value, from 1 to 64. The default is 1 ifM
is omitted.TINYINT[(
M
)] [UNSIGNED] [ZEROFILL]A very small integer. The signed range is
-128
to127
. The unsigned range is0
to255
.BOOL
,BOOLEAN
These types are synonyms for
TINYINT(1)
. 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
andFALSE
are merely aliases for1
and0
, 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 neither1
nor0
.SMALLINT[(
M
)] [UNSIGNED] [ZEROFILL]A small integer. The signed range is
-32768
to32767
. The unsigned range is0
to65535
.MEDIUMINT[(
M
)] [UNSIGNED] [ZEROFILL]A medium-sized integer. The signed range is
-8388608
to8388607
. The unsigned range is0
to16777215
.INT[(
M
)] [UNSIGNED] [ZEROFILL]A normal-size integer. The signed range is
-2147483648
to2147483647
. The unsigned range is0
to4294967295
.INTEGER[(
M
)] [UNSIGNED] [ZEROFILL]This type is a synonym for
INT
.BIGINT[(
M
)] [UNSIGNED] [ZEROFILL]A large integer. The signed range is
-9223372036854775808
to9223372036854775807
. The unsigned range is0
to18446744073709551615
.SERIAL
is an alias forBIGINT 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
orDOUBLE
values, so you should not use unsigned big integers larger than9223372036854775807
(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 aBIGINT
value to aDOUBLE
.MySQL can handle
BIGINT
in the following cases:- When using integers to store large unsigned values in a
BIGINT
column. - In
MIN(
orcol-name
)MAX(
, wherecol_name
)col_name
refers to aBIGINT
column. - When using operators (
+
,-
,*
, and so on) where both operands are integers.
- When using integers to store large unsigned values in a
- You can always store an exact integer value in a
BIGINT
column by storing it using a string. In this case, MariaDB performs a string-to-number conversion that involves no intermediate double-precision representation. - The
-
,+
, and*
operators useBIGINT
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 than9223372036854775807
.
- All arithmetic is done using signed
DECIMAL[(
M
[,D
])] [UNSIGNED] [ZEROFILL]A packed "exact" fixed-point number.
M
is the total number of digits (the precision) andD
is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the "-
" sign are not counted inM
. IfD
is 0, values have no decimal point or fractional part. The maximum number of digits (M
) forDECIMAL
is 65. The maximum number of supported decimals (D
) is 30. IfD
is omitted, the default is 0. IfM
is omitted, the default is 10.UNSIGNED
, if specified, disallows negative values.All basic calculations (
+, -, *, /
) withDECIMAL
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
. TheFIXED
synonym is available for compatibility with other database systems.FLOAT[(
M
,D
)] [UNSIGNED] [ZEROFILL]A small (single-precision) floating-point number. Permissible values are
-3.402823466E+38
to-1.175494351E-38
,0
, and1.175494351E-38
to3.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 andD
is the number of digits following the decimal point. IfM
andD
are omitted, values are stored to the limits permitted 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 MariaDB are done with double precision. See "Solving Problems with No Matching Rows".DOUBLE[(
M
,D
)] [UNSIGNED] [ZEROFILL]A normal-size (double-precision) floating-point number. Permissible values are
-1.7976931348623157E+308
to-2.2250738585072014E-308
,0
, and2.2250738585072014E-308
to1.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 andD
is the number of digits following the decimal point. IfM
andD
are omitted, values are stored to the limits permitted 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 theREAL_AS_FLOAT
SQL mode is enabled,REAL
is a synonym forFLOAT
rather thanDOUBLE
.FLOAT(
p
) [UNSIGNED] [ZEROFILL]A floating-point number.
p
represents the precision in bits, but MariaDB uses this value only to determine whether to useFLOAT
orDOUBLE
for the resulting data type. Ifp
is from 0 to 24, the data type becomesFLOAT
with noM
orD
values. Ifp
is from 25 to 53, the data type becomesDOUBLE
with noM
orD
values. The range of the resulting column is the same as for the single-precisionFLOAT
or double-precisionDOUBLE
data types described earlier in this section.FLOAT(
syntax is provided for ODBC compatibility.p
)