Home / SQL Statements / Question No: 220

Numbers can be exact-value literals or approximate-value literals. Exact-value literals are used just as given in SQL statements when possible and thus are not subject to the inexactness produced by rounding error. On the other hand, approximate-value literals are subject to rounding error and may not necessarily be used exactly as given.

Exact-value literals are written with no exponent. Approximate-value literals are written in scientific notation with an exponent. For example, the numeric values -43, 368.93, and .00214 are exact values, whereas -4.3E1, 3.6893E2, and 2.14E-3 are approximate values. Even though the two sets of numbers look like they have the same values, internally they are represented in different ways:

• Exact-value numbers are integer values with no fractional part after the decimal point or decimal values with a fractional part. They're represented internally like an integer or DECIMAL data type. Operations on integers are performed with the precision of BIGINT values (that is, 64 bits). Operations on decimal values have a precision of up to 64 decimal digits. Currently, the scale for decimal values allows up to 30 decimal digits after the decimal point.
• Approximate-value literals are represented as floating-point numbers (like the DOUBLE data type) and have a mantissa and exponent. The mantissa allows up to 53 bits of precision, which is about 15 decimal digits.

When numbers are used in an arithmetic or comparison operation, the result of the operation may depend on whether it involves exact or approximate values. Consider the following two comparisons:

mysql> SELECT 1.1 + 2.2 = 3.3, 1.1E0 + 2.2E0 = 3.3E0;

+-----------------+-------------------------+
| 1.1 + 2.2 = 3.3 | 1.1E0 + 2.2E0 = 3.3E0   |
+-----------------+-------------------------+
|               1 |                     0   |
+-----------------+-------------------------+

In the first expression, exact values are used, so the comparison involves exact calculations. In the second expression, approximate values are used and rounding error is possible. This illustrates that if you use approximate values in comparisons, you cannot expect exact-value precision. The internal representation of floating-point numbers inherently allows for the possibility of rounding error.

If you mix numbers with strings in numeric context, MySQL converts the strings to numbers and performs a numeric operation:

mysql> SELECT 1 + '1', 1 = '1';
+---------+---------+
| 1 + '1' | 1 = '1' |
+---------+---------+
|       2 |       1 |
+---------+---------+