MySQL 5.6 Reference Manual  /  ...  /  Numeric Data Type Syntax

### 11.1.1 Numeric Data Type Syntax

For integer data types, `M` indicates the maximum display width. The maximum display width is 255. Display width is unrelated to the range of values a type can store, as described in Section 11.1.6, “Numeric Type Attributes”.

For floating-point and fixed-point data 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 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.11, “Cast Functions and Operators”.

• A bit-value type. `M` indicates the number of bits per value, from 1 to 64. The default is 1 if `M` is omitted.

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

• A small integer. The signed range is `-32768` to `32767`. The unsigned range is `0` to `65535`.

• A medium-sized integer. The signed range is `-8388608` to `8388607`. The unsigned range is `0` to `16777215`.

• A normal-size integer. The signed range is `-2147483648` to `2147483647`. The unsigned range is `0` to `4294967295`.

• This type is a synonym for `INT`.

• 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:

• A packed exact fixed-point number. `M` is the total number of 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. (There is also a limit on how long the text of `DECIMAL` literals can be; see Section 12.21.3, “Expression Handling”.)

`UNSIGNED`, if specified, disallows negative values.

All basic calculations (`+, -, *, /`) with `DECIMAL` columns are done with a precision of 65 digits.

• These types are synonyms for `DECIMAL`. The `FIXED` synonym is available for compatibility with other database systems.

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

`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 permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

`FLOAT(M,D)` is a nonstandard MySQL extension.

`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.3.4.7, “Solving Problems with No Matching Rows”.

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

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

`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 permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.

`DOUBLE(M,D)` is a nonstandard MySQL extension.

`UNSIGNED`, if specified, disallows negative values.

• 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`.
{"ft_lang_label":"__label__en","ft_lang_prob":0.60090005,"math_prob":0.967372,"size":7943,"snap":"2020-34-2020-40","text_gpt3_token_len":2136,"char_repetition_ratio":0.18717723,"word_repetition_ratio":0.1673932,"special_character_ratio":0.34231398,"punctuation_ratio":0.15092936,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9871619,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-10-01T09:00:52Z\",\"WARC-Record-ID\":\"<urn:uuid:964e8761-e0e7-4ca0-86dd-26dc3d4cdd4d>\",\"Content-Length\":\"85179\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:45373e36-007f-4311-8ad5-5eda292fab74>\",\"WARC-Concurrent-To\":\"<urn:uuid:e3a601bf-8597-4e49-9e45-d25b1ac02716>\",\"WARC-IP-Address\":\"137.254.60.11\",\"WARC-Target-URI\":\"https://dev.mysql.com/doc/refman/5.6/en/numeric-type-syntax.html\",\"WARC-Payload-Digest\":\"sha1:RYBJFRKH5ET3KTVWFQQEEAPBFOU2BRTJ\",\"WARC-Block-Digest\":\"sha1:Q2SAN3LUX3H3BUMPFXZVUTJQNTGTHMUX\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-40/CC-MAIN-2020-40_segments_1600402124756.81_warc_CC-MAIN-20201001062039-20201001092039-00137.warc.gz\"}"}