This documentation is for an older version. If you're using the most current version, select the documentation for that version with the version switch in the upper right corner of the online documentation, or by downloading a newer PDF or EPUB file.

A summary of the numeric data types follows. For additional information about properties and storage requirements of the numeric types, see Section 11.2, “Numeric Types”, and Section 11.7, “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 11.2, “Numeric Types”. For
floating-point and fixed-point types,

`M`

If you specify `ZEROFILL`

for a numeric column,
MySQL 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 Section 12.10, “Cast Functions and Operators”.

A bit-field type.

indicates the number of bits per value, from 1 to 64. The default is 1 if`M`

is omitted.`M`

This data type was added in MySQL 5.0.3 for

`MyISAM`

, and extended in 5.0.5 to`MEMORY`

,`InnoDB`

,`BDB`

, and`NDBCLUSTER`

. Before 5.0.3,`BIT`

is a synonym for`TINYINT(1)`

.`TINYINT[(`

)] [UNSIGNED] [ZEROFILL]`M`

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)`

. A value of zero is considered false. Nonzero values are considered true:mysql>

+------------------------+ | IF(0, 'true', 'false') | +------------------------+ | false | +------------------------+ mysql>`SELECT IF(0, 'true', 'false');`

+------------------------+ | IF(1, 'true', 'false') | +------------------------+ | true | +------------------------+ mysql>`SELECT IF(1, 'true', 'false');`

+------------------------+ | IF(2, 'true', 'false') | +------------------------+ | true | +------------------------+`SELECT IF(2, 'true', 'false');`

However, the values

`TRUE`

and`FALSE`

are merely aliases for`1`

and`0`

, respectively, as shown here:mysql>

+--------------------------------+ | IF(0 = FALSE, 'true', 'false') | +--------------------------------+ | true | +--------------------------------+ mysql>`SELECT IF(0 = FALSE, 'true', 'false');`

+-------------------------------+ | IF(1 = TRUE, 'true', 'false') | +-------------------------------+ | true | +-------------------------------+ mysql>`SELECT IF(1 = TRUE, 'true', 'false');`

+-------------------------------+ | IF(2 = TRUE, 'true', 'false') | +-------------------------------+ | false | +-------------------------------+ mysql>`SELECT IF(2 = TRUE, 'true', 'false');`

+--------------------------------+ | IF(2 = FALSE, 'true', 'false') | +--------------------------------+ | false | +--------------------------------+`SELECT IF(2 = FALSE, 'true', 'false');`

The last two statements display the results shown because

`2`

is equal to neither`1`

nor`0`

.`SMALLINT[(`

)] [UNSIGNED] [ZEROFILL]`M`

A small integer. The signed range is

`-32768`

to`32767`

. The unsigned range is`0`

to`65535`

.`MEDIUMINT[(`

)] [UNSIGNED] [ZEROFILL]`M`

A medium-sized integer. The signed range is

`-8388608`

to`8388607`

. The unsigned range is`0`

to`16777215`

.`INT[(`

)] [UNSIGNED] [ZEROFILL]`M`

A normal-size integer. The signed range is

`-2147483648`

to`2147483647`

. The unsigned range is`0`

to`4294967295`

.`INTEGER[(`

)] [UNSIGNED] [ZEROFILL]`M`

This type is a synonym for

`INT`

.`BIGINT[(`

)] [UNSIGNED] [ZEROFILL]`M`

A large integer. The signed range is

`-9223372036854775808`

to`9223372036854775807`

. The unsigned range is`0`

to`18446744073709551615`

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

refers to a`col_name`

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

.

`DECIMAL[(`

[,`M`

])] [UNSIGNED] [ZEROFILL]`D`

For MySQL 5.0.3 and above:

A packed “exact” fixed-point number.

is the total number of digits (the precision) and`M`

is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the “`D`

`-`

” sign are not counted in. If`M`

is 0, values have no decimal point or fractional part. The maximum number of digits (`D`

) for`M`

`DECIMAL`

is 65 (64 from 5.0.3 to 5.0.5). The maximum number of supported decimals () is 30. If`D`

is omitted, the default is 0. If`D`

is omitted, the default is 10.`M`

`UNSIGNED`

, if specified, disallows negative values.All basic calculations (

`+, -, *, /`

) with`DECIMAL`

columns are done with a precision of 65 digits.Before MySQL 5.0.3:

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.is the total number of digits and`M`

is the number of digits after the decimal point. The decimal point and (for negative numbers) the “`D`

`-`

” sign are not counted in, although space for them is reserved. If`M`

is 0, values have no decimal point or fractional part. The maximum range of`D`

`DECIMAL`

values is the same as for`DOUBLE`

, but the actual range for a given`DECIMAL`

column may be constrained by the choice ofand`M`

. If`D`

is omitted, the default is 0. If`D`

is omitted, the default is 10.`M`

`UNSIGNED`

, if specified, disallows negative values.The behavior used by the server for

`DECIMAL`

columns in a table depends on the version of MySQL used to create the table. If your server is from MySQL 5.0.3 or higher, but you have`DECIMAL`

columns in tables that were created before 5.0.3, the old behavior still applies to those columns. To convert the tables to the newer`DECIMAL`

format, dump them with**mysqldump**and reload them.`DEC[(`

,[,`M`

])] [UNSIGNED] [ZEROFILL]`D`

`NUMERIC[(`

,[,`M`

])] [UNSIGNED] [ZEROFILL]`D`

`FIXED[(`

[,`M`

])] [UNSIGNED] [ZEROFILL]`D`

These types are synonyms for

`DECIMAL`

. The`FIXED`

synonym is available for compatibility with other database systems.`FLOAT[(`

,`M`

)] [UNSIGNED] [ZEROFILL]`D`

A small (single-precision) floating-point number. Permissible 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.is the total number of digits and`M`

is the number of digits following the decimal point. If`D`

and`M`

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

`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 B.5.4.7, “Solving Problems with No Matching Rows”.`DOUBLE[(`

,`M`

)] [UNSIGNED] [ZEROFILL]`D`

A normal-size (double-precision) floating-point number. Permissible 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.is the total number of digits and`M`

is the number of digits following the decimal point. If`D`

and`M`

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

`UNSIGNED`

, if specified, disallows negative values.`DOUBLE PRECISION[(`

,,`M`

)] [UNSIGNED] [ZEROFILL]`D`

`REAL[(`

,`M`

)] [UNSIGNED] [ZEROFILL]`D`

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(`

) [UNSIGNED] [ZEROFILL]`p`

A floating-point number.

represents the precision in bits, but MySQL uses this value only to determine whether to use`p`

`FLOAT`

or`DOUBLE`

for the resulting data type. Ifis from 0 to 24, the data type becomes`p`

`FLOAT`

with noor`M`

values. If`D`

is from 25 to 53, the data type becomes`p`

`DOUBLE`

with noor`M`

values. The range of the resulting column is the same as for the single-precision`D`

`FLOAT`

or double-precision`DOUBLE`

data types described earlier in this section.`FLOAT(`

syntax is provided for ODBC compatibility.)`p`