**Table of Contents**

MySQL 5.1 provides support for precision math: numeric value handling that results in extremely accurate results and a high degree control over invalid values. Precision math is based on these two features:

SQL modes that control how strict the server is about accepting or rejecting invalid data.

The MySQL library for fixed-point arithmetic.

These features have several implications for numeric operations:

**Precise calculations**: For exact-value numbers, calculations do not introduce floating-point errors. Instead, exact precision is used. For example, a number such as`.0001`

is treated as an exact value rather than as an approximation, and summing it 10,000 times produces a result of exactly`1`

, not a value that merely “close” to 1.**Well-defined rounding behavior**: For exact-value numbers, the result of`ROUND()`

depends on its argument, not on environmental factors such as how the underlying C library works.**Platform independence**: Operations on exact numeric values are the same across different platforms such as Windows and Unix.**Control over handling of invalid values**: Overflow and division by zero are detectable and can be treated as errors. For example, you can treat a value that is too large for a column as an error rather than having the value truncated to lie within the range of the column's data type. Similarly, you can treat division by zero as an error rather than as an operation that produces a result of`NULL`

. The choice of which approach to take is determined by the setting of the`sql_mode`

system variable.

An important result of these features is that MySQL 5.1 provides a high degree of compliance with standard SQL.

The following discussion covers several aspects of how precision
math works (including possible incompatibilities with older
applications). At the end, some examples are given that demonstrate
how MySQL 5.1 handles numeric operations precisely. For
information about using the `sql_mode`

system
variable to control the SQL mode, see
Section 5.1.7, “SQL Modes”.

The scope of precision math for exact-value operations includes
the exact-value data types (`DECIMAL`

and integer
types) and exact-value numeric literals. Approximate-value data
types and numeric literals still are handled as floating-point
numbers.

Exact-value numeric literals have an integer part or fractional
part, or both. They may be signed. Examples: `1`

,
`.2`

, `3.4`

,
`-5`

, `-6.78`

,
`+9.10`

.

Approximate-value numeric literals are represented in scientific
notation with a mantissa and exponent. Either or both parts may be
signed. Examples: `1.2E3`

,
`1.2E-3`

, `-1.2E3`

,
`-1.2E-3`

.

Two numbers that look similar need not be both exact-value or both
approximate-value. For example, `2.34`

is an
exact-value (fixed-point) number, whereas
`2.34E0`

is an approximate-value (floating-point)
number.

The `DECIMAL`

data type is a fixed-point type and
calculations are exact. In MySQL, the `DECIMAL`

type has several synonyms: `NUMERIC`

,
`DEC`

, `FIXED`

. The integer
types also are exact-value types.

The `FLOAT`

and `DOUBLE`

data
types are floating-point types and calculations are approximate.
In MySQL, types that are synonymous with `FLOAT`

or `DOUBLE`

are ```
DOUBLE
PRECISION
```

and `REAL`

.

This section discusses the characteristics of the
`DECIMAL`

data type (and its synonyms) in MySQL
5.1, with particular regard to the following topics:

Maximum number of digits

Storage format

Storage requirements

The non-standard MySQL extension to the upper range of

`DECIMAL`

columns

Possible incompatibilities with applications that are written for older versions of MySQL are noted throughout this section.

The declaration syntax for a `DECIMAL`

column is
`DECIMAL(`

.
The ranges of values for the arguments in MySQL 5.1
are as follows:
* M*,

`D`

is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL allowed a range of 1 to 254.)`M`

is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than`D`

.`M`

The maximum value of 65 for * M* means
that calculations on

`DECIMAL`

values are
accurate up to 65 digits. This limit of 65 digits of precision
also applies to exact-value numeric literals, so the maximum range
of such literals is different from before. (In older versions of
MySQL, decimal values could have up to 254 digits. However,
calculations were done using floating-point and thus were
approximate, not exact.)
Values for `DECIMAL`

columns in MySQL
5.1 are stored using a binary format that packs nine
decimal digits into four bytes. The storage requirements for the
integer and fractional parts of each value are determined
separately. Each multiple of nine digits requires four bytes, and
any digits left over require some fraction of four bytes. For
example, a `DECIMAL(18,9)`

column has nine digits
on either side of the decimal point, so the integer part and the
fractional part each require four bytes. A
`DECIMAL(20,10)`

column has ten digits on either
side of the decimal point. Each part requires four bytes for nine
of the digits, and one byte for the remaining digit.

The storage required for leftover digits is given by the following table:

Leftover Digits | Number of Bytes |

0 | 0 |

1 | 1 |

2 | 1 |

3 | 2 |

4 | 2 |

5 | 3 |

6 | 3 |

7 | 4 |

8 | 4 |

9 | 4 |

Unlike some older versions of MySQL (prior to 5.0.3),
`DECIMAL`

columns in MySQL 5.1 do
not store a leading `+`

character or
`-`

character or leading `0`

digits. If you insert `+0003.1`

into a
`DECIMAL(5,1)`

column, it is stored as
`3.1`

. For negative numbers, a literal
`-`

character is not stored. Applications that
rely on the older behavior must be modified to account for this
change.

`DECIMAL`

columns in MySQL 5.1 do
not allow values larger than the range implied by the column
definition. For example, a `DECIMAL(3,0)`

column
supports a range of `-999`

to
`999`

. A
`DECIMAL(`

column allows at most * M*,

`D`

`M`

`D`

`+`

sign.
The SQL standard requires that the precision of
`NUMERIC(`

be * M*,

`D`

`M`

`DECIMAL(``M`

,`D`

)

,
the standard requires a precision of at least
`M`

`DECIMAL(``M`

,`D`

)

and
`NUMERIC(``M`

,`D`

)

are the same, and both have a precision of exactly
`M`

For more detailed information about porting applications that rely
on the old treatment of the `DECIMAL`

data type,
see the *MySQL 5.0 Reference Manual*.

With precision math, exact-value numbers are used as given
whenever possible. For example, numbers in comparisons are used
exactly as given without a change in value. In strict SQL mode,
for `INSERT`

into a column with an exact data
type (`DECIMAL`

or integer), a number is inserted
with its exact value if it is within the column range. When
retrieved, the value should be the same as what was inserted.
(Without strict mode, truncation for `INSERT`

is
allowable.)

Handling of a numeric expression depends on what kind of values the expression contains:

If any approximate values are present, the expression is approximate and is evaluated using floating-point arithmetic.

If no approximate values are present, the expression contains only exact values. If any exact value contains a fractional part (a value following the decimal point), the expression is evaluated using

`DECIMAL`

exact arithmetic and has a precision of 65 digits. (The term “exact” is subject to the limits of what can be represented in binary. For example,`1.0/3.0`

can be approximated in decimal notation as`.333...`

, but not written as an exact number, so`(1.0/3.0)*3.0`

does not evaluate to exactly`1.0`

.)Otherwise, the expression contains only integer values. The expression is exact and is evaluated using integer arithmetic and has a precision the same as

`BIGINT`

(64 bits).

If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate.

Inserts into numeric columns are affected by the SQL mode, which
is controlled by the `sql_mode`

system variable.
(See Section 5.1.7, “SQL Modes”.) The following discussion
mentions strict mode (selected by the
`STRICT_ALL_TABLES`

or
`STRICT_TRANS_TABLES`

mode values) and
`ERROR_FOR_DIVISION_BY_ZERO`

. To turn on all
restrictions, you can simply use `TRADITIONAL`

mode, which includes both strict mode values and
`ERROR_FOR_DIVISION_BY_ZERO`

:

mysql>`SET sql_mode='TRADITIONAL';`

If a number is inserted into an exact type column
(`DECIMAL`

or integer), it is inserted with its
exact value if it is within the column range.

If the value has too many digits in the fractional part, rounding occurs and a warning is generated. Rounding is done as described in Section 28.4, “Rounding Behavior”.

If the value has too many digits in the integer part, it is too large and is handled as follows:

If strict mode is not enabled, the value is truncated to the nearest legal value and a warning is generated.

If strict mode is enabled, an overflow error occurs.

Underflow is not detected, so underflow handing is undefined.

By default, division by zero produces a result of
`NULL`

and no warning. With the
`ERROR_FOR_DIVISION_BY_ZERO`

SQL mode enabled,
MySQL handles division by zero differently:

If strict mode is not enabled, a warning occurs.

If strict mode is enabled, inserts and updates involving division by zero are prohibited, and an error occurs.

In other words, inserts and updates involving expressions that
perform division by zero can be treated as errors, but this
requires `ERROR_FOR_DIVISION_BY_ZERO`

in addition
to strict mode.

Suppose that we have this statement:

INSERT INTO t SET i = 1/0;

This is what happens for combinations of strict and
`ERROR_FOR_DIVISION_BY_ZERO`

modes:

`sql_mode` Value | Result |

`''` (Default) | No warning, no error; `i` is set to
`NULL` . |

strict | No warning, no error; `i` is set to
`NULL` . |

`ERROR_FOR_DIVISION_BY_ZERO` | Warning, no error; `i` is set to
`NULL` . |

strict,`ERROR_FOR_DIVISION_BY_ZERO` | Error condition; no row is inserted. |

For inserts of strings into numeric columns, conversion from string to number is handled as follows if the string has non-numeric contents:

A string that does not begin with a number cannot be used as a number and produces an error in strict mode, or a warning otherwise.

*This includes the empty string*.A string that begins with a number can be converted, but the trailing non-numeric portion is truncated. If the truncated portion contains anything other than spaces, this produces an error in strict mode, or a warning otherwise.

This section discusses precision math rounding for the
`ROUND()`

function and for inserts
into columns with exact-value types (`DECIMAL`

and integer).

The `ROUND()`

function rounds
differently depending on whether its argument is exact or
approximate:

For exact-value numbers,

`ROUND()`

uses the “round half up” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.For approximate-value numbers, the result depends on the C library. On many systems, this means that

`ROUND()`

uses the “round to nearest even” rule: A value with any fractional part is rounded to the nearest even integer.

The following example shows how rounding differs for exact and approximate values:

mysql>+------------+--------------+ | ROUND(2.5) | ROUND(25E-1) | +------------+--------------+ | 3 | 2 | +------------+--------------+`SELECT ROUND(2.5), ROUND(25E-1);`

For inserts into a `DECIMAL`

or integer column,
the target is an exact data type, so rounding uses “round
half up,” regardless of whether the value to be inserted is
exact or approximate:

mysql>Query OK, 0 rows affected (0.00 sec) mysql>`CREATE TABLE t (d DECIMAL(10,0));`

Query OK, 2 rows affected, 2 warnings (0.00 sec) Records: 2 Duplicates: 0 Warnings: 2 mysql>`INSERT INTO t VALUES(2.5),(2.5E0);`

+------+ | d | +------+ | 3 | | 3 | +------+`SELECT d FROM t;`

This section provides some examples that show precision math query results in MySQL 5.1.

**Example 1**. Numbers are used with
their exact value as given when possible:

mysql>+--------------+ | .1 + .2 = .3 | +--------------+ | 1 | +--------------+`SELECT .1 + .2 = .3;`

For floating-point values, results are inexact:

mysql>+--------------------+ | .1E0 + .2E0 = .3E0 | +--------------------+ | 0 | +--------------------+`SELECT .1E0 + .2E0 = .3E0;`

Another way to see the difference in exact and approximate value
handling is to add a small number to a sum many times. Consider
the following stored procedure, which adds
`.0001`

to a variable 1,000 times.

CREATE PROCEDURE p () BEGIN DECLARE i INT DEFAULT 0; DECLARE d DECIMAL(10,4) DEFAULT 0; DECLARE f FLOAT DEFAULT 0; WHILE i < 10000 DO SET d = d + .0001; SET f = f + .0001E0; SET i = i + 1; END WHILE; SELECT d, f; END;

The sum for both `d`

and `f`

logically should be 1, but that is true only for the decimal
calculation. The floating-point calculation introduces small
errors:

+--------+------------------+ | d | f | +--------+------------------+ | 1.0000 | 0.99999999999991 | +--------+------------------+

**Example 2**. Multiplication is
performed with the scale required by standard SQL. That is, for
two numbers * X1* and

`X2`

`S1`

`S2`

`S1`

+ `S2`

:
mysql>+-----------+ | .01 * .01 | +-----------+ | 0.0001 | +-----------+`SELECT .01 * .01;`

**Example 3**. Rounding behavior is
well-defined:

Rounding behavior (for example, with the
`ROUND()`

function) is independent
of the implementation of the underlying C library, which means
that results are consistent from platform to platform.

Rounding for exact-value columns (`DECIMAL`

and
integer) and exact-valued numbers uses the “round half
up” rule. Values with a fractional part of .5 or greater
are rounded away from zero to the nearest integer, as shown here:

mysql>+------------+-------------+ | ROUND(2.5) | ROUND(-2.5) | +------------+-------------+ | 3 | -3 | +------------+-------------+`SELECT ROUND(2.5), ROUND(-2.5);`

However, rounding for floating-point values uses the C library, which on many systems uses the “round to nearest even” rule. Values with any fractional part on such systems are rounded to the nearest even integer:

mysql>+--------------+---------------+ | ROUND(2.5E0) | ROUND(-2.5E0) | +--------------+---------------+ | 2 | -2 | +--------------+---------------+`SELECT ROUND(2.5E0), ROUND(-2.5E0);`

**Example 4**. In strict mode,
inserting a value that is too large results in overflow and causes
an error, rather than truncation to a legal value.

When MySQL is not running in strict mode, truncation to a legal value occurs:

mysql>Query OK, 0 rows affected (0.00 sec) mysql>`SET sql_mode='';`

Query OK, 0 rows affected (0.01 sec) mysql>`CREATE TABLE t (i TINYINT);`

Query OK, 1 row affected, 1 warning (0.00 sec) mysql>`INSERT INTO t SET i = 128;`

+------+ | i | +------+ | 127 | +------+ 1 row in set (0.00 sec)`SELECT i FROM t;`

Howver, an overflow condition occurs if strict mode is in effect:

mysql>Query OK, 0 rows affected (0.00 sec) mysql>`SET sql_mode='STRICT_ALL_TABLES';`

Query OK, 0 rows affected (0.00 sec) mysql>`CREATE TABLE t (i TINYINT);`

ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1 mysql>`INSERT INTO t SET i = 128;`

Empty set (0.00 sec)`SELECT i FROM t;`

**Example 5**: In strict mode and
with `ERROR_FOR_DIVISION_BY_ZERO`

set, division
by zero causes an error, and not a result of
`NULL`

.

In non-strict mode, division by zero has a result of
`NULL`

:

mysql>Query OK, 0 rows affected (0.01 sec) mysql>`SET sql_mode='';`

Query OK, 0 rows affected (0.00 sec) mysql>`CREATE TABLE t (i TINYINT);`

Query OK, 1 row affected (0.00 sec) mysql>`INSERT INTO t SET i = 1 / 0;`

+------+ | i | +------+ | NULL | +------+ 1 row in set (0.03 sec)`SELECT i FROM t;`

However, division by zero is an error if the proper SQL modes are in effect:

mysql>Query OK, 0 rows affected (0.00 sec) mysql>`SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';`

Query OK, 0 rows affected (0.00 sec) mysql>`CREATE TABLE t (i TINYINT);`

ERROR 1365 (22012): Division by 0 mysql>`INSERT INTO t SET i = 1 / 0;`

Empty set (0.01 sec)`SELECT i FROM t;`

**Example 6**. Prior to MySQL 5.0.3
(before precision math was introduced), exact-value and
approximate-value literals both are converted to double-precision
floating-point values:

mysql>+------------+ | VERSION() | +------------+ | 4.1.18-log | +------------+ 1 row in set (0.01 sec) mysql>`SELECT VERSION();`

Query OK, 1 row affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql>`CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;`

+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | double(3,1) | | | 0.0 | | | b | double | | | 0 | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.04 sec)`DESCRIBE t;`

As of MySQL 5.0.3, the approximate-value literal still is
converted to floating-point, but the exact-value literal is
handled as `DECIMAL`

:

mysql>+-----------------+ | VERSION() | +-----------------+ | 5.1.6-alpha-log | +-----------------+ 1 row in set (0.11 sec) mysql>`SELECT VERSION();`

Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql>`CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;`

+-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | a | decimal(2,1) unsigned | NO | | 0.0 | | | b | double | NO | | 0 | | +-------+-----------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)`DESCRIBE t;`

**Example 7**. If the argument to an
aggregate function is an exact numeric type, the result is also an
exact numeric type, with a scale at least that of the argument.

Consider these statements:

mysql>mysql>`CREATE TABLE t (i INT, d DECIMAL, f FLOAT);`

mysql>`INSERT INTO t VALUES(1,1,1);`

`CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;`

Result before MySQL 5.0.3 (prior to the introduction of precision math in MySQL):

mysql>+--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | AVG(i) | double(17,4) | YES | | NULL | | | AVG(d) | double(17,4) | YES | | NULL | | | AVG(f) | double | YES | | NULL | | +--------+--------------+------+-----+---------+-------+`DESCRIBE y;`

The result is a double no matter the argument type.

Result as of MySQL 5.0.3:

mysql>+--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | AVG(i) | decimal(14,4) | YES | | NULL | | | AVG(d) | decimal(14,4) | YES | | NULL | | | AVG(f) | double | YES | | NULL | | +--------+---------------+------+-----+---------+-------+`DESCRIBE y;`

The result is a double only for the floating-point argument. For exact type arguments, the result is also an exact type.