Numeric Datatypes

Numeric datatypes store numbers. MySQL supports several numeric datatypes, each with a different range of numbers that can be stored in it. Obviously, the larger the supported range, the more storage space needed. In addition, some numeric datatypes support the use of decimal points (and fractional numbers) whereas others support only whole numbers. Table D.2 lists the frequently used MySQL numeric datatypes.

Table D.2. Numeric Datatypes

Datatype	Description
BIT	A bit-field, from 1 to 64 bits wide. (Prior to MySQL 5 BIT was functionally equivalent to TINYINT.)
BIGINT	Integer value, supports numbers from -9223372036854775808 to 9223372036854775807 (or 0 to 18446744073709551615 if UNSIGNED).
BOOLEAN (or BOOL)	Boolean flag, either 0 or 1, used primarily for on/off flags.
DECIMAL (or DEC)	Floating point values with varying levels of precision.
DOUBLE	Double-precision floating point values.
FLOAT	Single-precision floating point values.
INT (or INTEGER)	Integer value, supports numbers from -2147483648 to 2147483647 (or 0 to 4294967295 if UNSIGNED).
MEDIUMINT	Integer value, supports numbers from -8388608 to 8388607 (or 0 to 16777215 if UNSIGNED).
REAL	4-byte floating point values.
SMALLINT	Integer value, supports numbers from -32768 to 32767 (or 0 to 65535 if UNSIGNED)
TINYINT	Integer value, supports numbers from -128 to 127 (or 0 to 255 if UNSIGNED).

Note

Signed Or UNSIGNED? All numeric datatypes (with the exception of BIT and BOOLEAN) can be signed or unsigned. Signed numeric columns can store both positive and negative numbers, unsigned numeric columns store only positive numbers. Signed is the default, but if you know that you'll not need to store negative values you can use the UNSIGNED keyword, doing so will allow you to store values twice as large.

Tip

Not Using Quotes Unlike strings, numeric values should never be enclosed within quotes.

Tip

Storing Currency There is no special MySQL datatype for currency values, use DECIMAL(8,2) instead.