MySQL Reference Manual :: 11.1.1 Overview of Numeric Types

11.1.1. Overview of Numeric Types

A summary of the numeric column types follows. For additional information, see Section 11.2, “Numeric Types”. Column storage requirements are given in Section 11.5, “Column Type Storage Requirements”.

M indicates the maximum display width. The maximum legal display width is 255. Display width is unrelated to the storage size or range of values a type can contain, as described in Section 11.2, “Numeric Types”.

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT.

SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

Warning: You should be aware that when you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned. See Section 12.8, “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 if M is omitted.

This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, and BDB. Before 5.0.3, BIT is a synonym for TINYINT(1).
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 are synonyms for TINYINT(1). The BOOLEAN synonym was added in MySQL 4.1.0. 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-size 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 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 shouldn't 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 4.0 can handle BIGINT in the following cases:
  - When using integers to store big 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(p) [UNSIGNED] [ZEROFILL]

A floating-point number. p represents the precision. It can be from 0 to 24 for a single-precision floating-point number and from 25 to 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(p) has the same range as the corresponding FLOAT and DOUBLE types, but the display width and number of decimals are undefined.

As of MySQL 3.23, this is a true floating-point value. In earlier MySQL versions, FLOAT(p) always has two decimals.

This syntax is provided for ODBC compatibility.

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”.
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. If UNSIGNED is specified, negative values are disallowed. M is the display width and D is the number of significant digits. FLOAT without arguments or FLOAT(p) (where p is in the range from 0 to 24) stands for a single-precision floating-point number.
DOUBLE[(M,B)] [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. If UNSIGNED is specified, negative values are disallowed. M is the display width and B is the number of bits of precision. DOUBLE without arguments or FLOAT(p) (where p is in the range from 25 to 53) stands for a double-precision floating-point number. A single-precision floating-point number is accurate to approximately 7 decimal places; a double-precision floating-point number is accurate to approximately 15 decimal places.
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL] , REAL[(M,D)] [UNSIGNED] [ZEROFILL]

These are synonyms for DOUBLE. Exception: If the server SQL mode includes the REAL_AS_FLOAT option, REAL is a synonym for FLOAT rather than DOUBLE.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

For MySQL 5.0.3 and above:

A packed ``exact'' fixed-point number. M is the total number of digits and D is the number of decimals. 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 64. The maximum number of supported decimals (D) is 30. If UNSIGNED is specified, negative values are disallowed.

If D is omitted, the default is 0. If M is omitted, the default is 10.

All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 64 decimal 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. M is the total number of digits and D is the number of decimals. The decimal point and (for negative numbers) the '-' sign are not counted in M, although space for them is reserved. If D is 0, values have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If UNSIGNED is specified, negative values are disallowed.

If D is omitted, the default is 0. If M is omitted, the default is 10.

Before MySQL 3.23:

As just described, with the exception that the M value must be large enough to include the space needed for the sign and the decimal point characters.
DEC[(M[,D])] [UNSIGNED] [ZEROFILL] , NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL] , FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

These are synonyms for DECIMAL. The FIXED synonym was added in MySQL 4.1.0 for compatibility with other servers.

Previous / Next / Up / Table of Contents

User Comments

Warning: query failed: Unknown column 'user.firstname' in 'field list' in /data0/sites/live/web-main/lib/mysql-cxn.php on line 69

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data0/sites/live/web-main/lib/docbook.php on line 245

Add your own comment.

Top / Previous / Next / Up / Table of Contents

MySQL Reference Manual :: 11.1.1 Overview of Numeric Types

Search the MySQL manual:

Additional languages

Additional formats

11.1.1. Overview of Numeric Types

User Comments