Table of Contents for

MySQL in a Nutshell, 2nd Edition

Standard SQL numeric data types are allowed: accurate numeric data types (i.e., BIGINT, DECIMAL, INTEGER, MEDIUMINT, NUMERIC, SMALLINT, and TINYINT) and approximate numeric data types (i.e., DOUBLE and FLOAT). For all numeric data types, you can use the UNSIGNED and ZEROFILL flags depending on your needs. If UNSIGNED is omitted, SIGNED is assumed. A numeric data type has different allowable ranges based on whether it’s SIGNED or UNSIGNED. The ZEROFILL flag instructs MySQL to pad the unused spaces to the left of a number with zeros. For example, a column with a data type set to INT(10) using ZEROFILL will display the number 5 as 0000000005. If the ZEROFILL flag is used, UNSIGNED is assumed for the column. When subtracting values where one is UNSIGNED, the results will become UNSIGNED.

For several of the numeric data types, you can specify a width for displaying. This number cannot exceed 255. The display width is a factor only when ZEROFILL is used for the column. You may also specify the number of digits allowed for the decimals, including the decimal point.

Approximate numeric data types store floating-point numbers such as fractions where an approximation must be made. For instance, an accurate number, per se, cannot be stored for 1/3 because the decimal point for 3 continues on endlessly. MySQL provides two approximate numeric data types: FLOAT and DOUBLE with their synonyms.

Following is a list of numeric data types. They’re not organized alphabetically; instead, they’re organized in ascending order based on the size of numeric values they can contain, with the column data types that may be used for approximate numbers listed last.