Numeric Types

MySQL provides numeric types for integer and floating point values. Types may be chosen according to the range of values you need to represent.

For integer types, a column must be a PRIMARY KEY or a UNIQUE index if the AUTO_INCREMENT attribute is specified. Inserting NULL into an AUTO_INCREMENT column inserts a value that is one greater than the column's current maximum value. Negative values are not allowed for integer types if the UNSIGNED attribute is specified.

Values for numeric types are padded with leading zeroes to the column's display width if the ZEROFILL attribute is specified.

TINYINT[(M)]

Meaning. A very small integer

Allowable attributes. AUTO_INCREMENT, UNSIGNED, ZEROFILL

Range. –128 to 127 (–2⁷ to 2⁷–1), or 0 to 255 (0 to 2⁸–1) if UNSIGNED

Default value. NULL if column can be NULL, 0 if NOT NULL

Storage required. 1 byte

SMALLINT[(M)]

Meaning. A small integer

Allowable attributes. AUTO_INCREMENT, UNSIGNED, ZEROFILL

Range. –32768 to 32767 (–2¹⁵ to 2¹⁵–1), or 0 to 65535 (0 to 2¹⁶–1) if UNSIGNED

Default value. NULL if column can be NULL, 0 if NOT NULL

Storage required. 2 bytes

MEDIUMINT[(M)]

Meaning. A medium-sized integer

Allowable attributes. AUTO_INCREMENT, UNSIGNED, ZEROFILL

Range. –8388608 to 8388607 (–2²³ to 2²³–1), or 0 to 16777215 (0 to 2²⁴–1) if UNSIGNED

Default value. NULL if column can be NULL, 0 if NOT NULL

Storage required. 3 bytes

INT[(M)]

Meaning. A normal-sized integer

Allowable attributes. AUTO_INCREMENT, UNSIGNED, ZEROFILL

Range. –2147483648 to 2147483647 (–2³¹ to 2³¹–1), or 0 to 4294967295 (0 to 2³²–1) if UNSIGNED

Default value. NULL if column can be NULL, 0 if NOT NULL

Storage required. 4 bytes

Synonyms. INTEGER[(M)]

BIGINT[(M)]

Meaning. A large integer

Allowable attributes. AUTO_INCREMENT, UNSIGNED, ZEROFILL

Range. –9223372036854775808 to 9223372036854775807 (–2⁶³ to 2⁶³–1), or 0 to 18446744073709551615 (0 to 2⁶⁴–1) if UNSIGNED

Default value. NULL if column can be NULL, 0 if NOT NULL

Storage required. 8 bytes

FLOAT[(M,D)]

Meaning. A small floating-point number; single-precision (less precise than DOUBLE)

Allowable attributes. ZEROFILL

Range. Minimum non-zero values are ±1.175494351E–38; maximum non-zero values are ±3.402823466E+38

Default value. NULL if column can be NULL, 0 if NOT NULL

Storage required. 4 bytes

Synonyms. Prior to MySQL 3.23, FLOAT(4) is a synonym for FLOAT with the default M and D values.

Note. As of MySQL 3.23, FLOAT(4) is a true floating-point type (values are stored to the full precision allowed by your hardware, not rounded to the default number of decimal places).

DOUBLE[(M,D)]

Meaning. A large floating-point number; double-precision (more precise than FLOAT)

Allowable attributes. ZEROFILL

Range. Minimum non-zero values are ±2.2250738585072014E–308, maximum non-zero values are ±1.7976931348623157E+308

Default value. NULL if column can be NULL, 0 if NOT NULL

Storage required. 8 bytes

Synonyms. DOUBLE PRECISION[(M,D)] and REAL[(M,D)] are synonyms for DOUBLE[(M,D)] Prior to MySQL 3.23, FLOAT(8) is a synonym for DOUBLE with the default M and D values.

Note. As of MySQL 3.23, FLOAT(8) is a true floating-point type (Values are stored to the full precision allowed by your hardware, not rounded to the default number of decimal places.)

DECIMAL(M,D)

Meaning. A floating-point number, stored as a string (1 byte per digit, decimal point, or '-' sign).

Allowable attributes. ZEROFILL

Range. Maximum range is the same as for DOUBLE; effective range for a given DECIMAL type is determined by M and D. If D is 0, column values have no decimal point or a fractional part.

Default value. NULL if column can be NULL, 0 if NOT NULL

Storage required. M bytes for versions of MySQL earlier than 3.23, M+2 bytes for MySQL 3.23 and up

Synonyms. NUMERIC(M,D)

Note. As of MySQL 3.23, the value of M does not include the bytes needed for the sign character or decimal point, in conformance with ANSISQL.