MENU


Data Types Numeric

Integer Type Bytes Range
TINYINT 1 -128 to 127
SMALLINT 2 -32 768 to 32 767
MEDIUMINT 3 -8 388 608 to 8 388 607
INT 4 -2 147 483 648 to 2 147 483 647
BIGINT 8 -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807
You can append an UNSIGNED attribute to an integer type, so that only non-negative integers can be stored. This increases the upper limit of the type. For instance, ‘TINYINT UNSIGNED’ stores integers from 0 to 255.
You can append a bracketed integer to an integer type, to set the display width. For example, for the type ‘TINYINT (5)’, a value of 10 will be left-padded with three spaces when displayed.
You can append a ZEROFILL attribute to an integer type with a display width, so that the integers are left-padded with zeros when displayed. For example, for the type ‘TINYINT(5) ZEROFILL’, a value of 10 will be displayed as 00010.

When ZEROFILL is specified, the UNSIGNED attribute is automatically added.
Point Type
DECIMAL(8,3), for instance, can be used to store exact values from -99999.999 to 99999.999, where the precision is 8 digits and the scale is 3. The number of bytes used varies.

DECIMAL(10) is the same as DECIMAL(10,0).
DECIMAL is the same as DECIMAL(10) in MySQL.
DECIMAL(8,3) is the same as NUMERIC(8,3).
FLOAT, REAL, DOUBLE and DOUBLE PRECISION store approximate numeric values. REAL, DOUBLE, and DOUBLE PRECISION are the same. In MySQL, single-precision values use 4 bytes, while double-precision values use 8 bytes.
You can specify the precision p using the syntax FLOAT(p). If necessary, that will use double precision for the storage.

MySQL allows the nonstandard syntax: FLOAT(M,D) or REAL(M,D), DOUBLE(M,D) or DOUBLE PRECISION(M,D), where M is the total number of digits, and D is the number of digits after the decimal point.
You can also append UNSIGNED to these types, to prevent negative values from being stored. However, the upper limits of the ranges are not increased.
Bit Type
BIT(10), for instance, can be used to store values of 10 bits. A binary representation is specified using a notation like b’1001010101’.