6.2.1 Numeric Types

MySQL supports all of the SQL-92 numeric datatypes. These types include the exact numeric datatypes (NUMERIC, DECIMAL, INTEGER, and SMALLINT), as well as the approximate numeric datatypes (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.

The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the SQL-92 standard. They are used for values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example:

    salary DECIMAL(5,2)

In this example, 5 (precision) represents the number of significant decimal digits that will be stored for values, and 2 (scale) represents the number of digits that will be stored following the decimal point. In this case, therefore, the range of values that can be stored in the salary column is from -99.99 to 99.99. (MySQL can actually store numbers up to 999.99 in this column because it doesn't have to store the sign for positive numbers)

In SQL-92, the syntax DECIMAL(p) is equivalent to DECIMAL(p,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(p,0), where the implementation is allowed to decide the value of p. MySQL does not currently support either of these variant forms of the DECIMAL/NUMERIC datatypes. This is not generally a serious problem, as the principal benefits of these types derive from the ability to control both precision and scale explicitly.

DECIMAL and NUMERIC values are stored as strings, rather than as binary floating-point numbers, in order to preserve the decimal precision of those values. One character is used for each digit of the value, the decimal point (if scale > 0), and the `-' sign (for negative numbers). If scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.

The maximum range of DECIMAL and NUMERIC values is the same as for DOUBLE, but the actual range for a given DECIMAL or NUMERIC column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is rounded to that scale. When a DECIMAL or NUMERIC column is assigned a value whose magnitude exceeds the range implied by the specified (or defaulted) precision and scale, MySQL stores the value representing the corresponding end point of that range.

As an extension to the SQL-92 standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT as listed in the tables above. Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional width specification is used to left-pad the display of values whose width is less than the width specified for the column, but does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values whose width exceeds that specified for the column. When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeroes. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, as in these cases MySQL trusts that the data did fit into the original column width.

All integer types can have an optional (non-standard) attribute UNSIGNED. Unsigned values can be used when you want to allow only positive numbers in a column and you need a little bigger numeric range for the column.

As of MySQL 4.0.2, floating-point types also can be UNSIGNED. As with integer types, this attribute prevents negative values from being stored in the column. Unlike the integer types, the upper range of column values remains the same.

The FLOAT type is used to represent approximate numeric datatypes. The SQL-92 standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. The MySQL implementation also supports this optional precision specification. When the keyword FLOAT is used for a column type without a precision specification, MySQL uses four bytes to store the values. A variant syntax is also supported, with two numbers given in parentheses following the FLOAT keyword. With this option, the first number continues to represent the storage requirements for the value in bytes, and the second number specifies the number of digits to be stored and displayed following the decimal point (as with DECIMAL and NUMERIC). When MySQL is asked to store a number for such a column with more decimal digits following the decimal point than specified for the column, the value is rounded to eliminate the extra digits when the value is stored.

The REAL and DOUBLE PRECISION types do not accept precision specifications. As an extension to the SQL-92 standard, MySQL recognises DOUBLE as a synonym for the DOUBLE PRECISION type. In contrast with the standard's requirement that the precision for REAL be smaller than that used for DOUBLE PRECISION, MySQL implements both as 8-byte double-precision floating-point values (when not running in ``ANSI mode''). For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of decimal points.

When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

For example, the range of an INT column is -2147483648 to 2147483647. If you try to insert -9999999999 into an INT column, the value is clipped to the lower endpoint of the range, and -2147483648 is stored instead. Similarly, if you try to insert 9999999999, 2147483647 is stored instead.

If the INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift up to 0 and 4294967295. If you try to store -9999999999 and 9999999999, the values stored in the column become 0 and 4294967296.

Conversions that occur due to clipping are reported as ``warnings'' for ALTER TABLE, LOAD DATA INFILE, UPDATE, and multi-row INSERT statements.

User Comments

DECIMAL appears to be handled differently on
different versions/platforms. For example, in version
3.23.47-nt, DECIMAL(5,2) has a range of -999.99
to 9999.99 (the negative sign takes up a space
since it is stored as a string). But in version 3.22.32-
log (? on unix; I have limited access) DECIMAL(5,2)
has a range of -9.99 to 99.99

Create table with the number data-types:
CREATE TABLE TestInt (
tinyi tinyint(1) unsigned NOT NULL default '0',
smalli smallint(1) unsigned NOT NULL default '0',
medi mediumint(1) unsigned NOT NULL default '0',
regInt int(1) unsigned NOT NULL default '0'
) TYPE=MyISAM; Now insert the MAX number +1:
INSERT INTO `TestInt' (`tinyi`, `smalli`, `medi`,
`regInt`) VALUES (256, 65536,
16777216,4294967296); Notice that mySQL put
the MAX value for the type into the database. Now
ALTER TABLE `TestInt` CHANGE `regInt` `regInt`
INT(11) UNSIGNED DEFAULT '0' NOT NULL and then
Insert (11*4294967295), result is 4294967295.
The point: number(xxx) is meaningless as far as I
can tell, if you want something that can handle 16
million numbers use mediumint or if you need an
autoincrement ID for a lookup use tinyint. NOTE:
PHPmyAdmin Insert function does not allow you to
enter a number greater then 4 digits, use the SQL
query window.

Using a standard table and column naming scheme
will eliminate any possible conflict with reserved
words. For example, use "tbl_events" or "tblEvents"
instead of "Events" as your table name. In many
cases, one site has a single MySQL database (as in
the case with most web hosts), so vague table
names like "events" could conflict with other things
that the user has in the database. In these cases,
using a "project_table" scheme for table naming is a
very good idea. For example, use "link_categories"
for a link directory manager system, instead of
just "categories".

Add your own comment.