Menu:
precision-math-examples:: Precision Math Examples
MySQL 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 and provide a high degree of compliance with standard SQL:
Precise calculations: For exact-value numbers, calculations do not introduce floating-point errors. Instead, exact precision is used. For example, MySQL treats a number such as '.0001' 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 is 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 server SQL mode.
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 handles numeric operations precisely. For information about controlling the SQL mode, see *note sql-mode::.
File: manual.info.tmp, Node: precision-math-numbers, Next: precision-math-decimal-characteristics, Prev: precision-math, Up: precision-math
The scope of precision math for exact-value operations includes the exact-value data types (integer and *note 'DECIMAL': fixed-point-types. types) and exact-value numeric literals. Approximate-value data types and numeric literals 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 may be treated differently. For example, '2.34' is an exact-value (fixed-point) number, whereas '2.34E0' is an approximate-value (floating-point) number.
The note 'DECIMAL': fixed-point-types. data type is a fixed-point type and calculations are exact. In MySQL, the note 'DECIMAL': fixed-point-types. type has several synonyms: note 'NUMERIC': fixed-point-types, note 'DEC': fixed-point-types, *note 'FIXED': fixed-point-types. The integer types also are exact-value types.
The note 'FLOAT': floating-point-types. and note 'DOUBLE': floating-point-types. data types are floating-point types and calculations are approximate. In MySQL, types that are synonymous with note 'FLOAT': floating-point-types. or note 'DOUBLE': floating-point-types. are note 'DOUBLE PRECISION': floating-point-types. and note 'REAL': floating-point-types.
File: manual.info.tmp, Node: precision-math-decimal-characteristics, Next: precision-math-expressions, Prev: precision-math-numbers, Up: precision-math
This section discusses the characteristics of the *note 'DECIMAL': fixed-point-types. data type (and its synonyms), with particular regard to the following topics:
Maximum number of digits
Storage format
Storage requirements
The nonstandard MySQL extension to the upper range of *note 'DECIMAL': fixed-point-types. columns
The declaration syntax for a *note 'DECIMAL': fixed-point-types. column is 'DECIMAL(M,D)'. The ranges of values for the arguments are as follows:
M is the maximum number of digits (the precision). It has a range of 1 to 65.
D 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 M.
If D is omitted, the default is 0. If M is omitted, the default is 10.
The maximum value of 65 for M means that calculations on note 'DECIMAL': fixed-point-types. 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 differs from before. (There is also a limit on how long the text of note 'DECIMAL': fixed-point-types. literals can be; see *note precision-math-expressions::.)
Values for *note 'DECIMAL': fixed-point-types. columns are stored using a binary format that packs nine decimal digits into 4 bytes. The storage requirements for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes. The storage required for remaining digits is given by the following table.
Leftover Digits Number of Bytes
0 0
1-2 1
3-4 2
5-6 3
7-9 4
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 4 bytes. A 'DECIMAL(20,6)' column has fourteen integer digits and six fractional digits. The integer digits require four bytes for nine of the digits and 3 bytes for the remaining five digits. The six fractional digits require 3 bytes.
*note 'DECIMAL': fixed-point-types. columns 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.
*note 'DECIMAL': fixed-point-types. columns do not permit 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(M,D)' column permits up to M - D digits to the left of the decimal point.
The SQL standard requires that the precision of 'NUMERIC(M,D)' be exactly M digits. For 'DECIMAL(M,D)', the standard requires a precision of at least M digits but permits more. In MySQL, 'DECIMAL(M,D)' and 'NUMERIC(M,D)' are the same, and both have a precision of exactly M digits.
For a full explanation of the internal format of 'DECIMAL' values, see the file 'strings/decimal.c' in a MySQL source distribution. The format is explained (with an example) in the 'decimal2bin()' function.
File: manual.info.tmp, Node: precision-math-expressions, Next: precision-math-rounding, Prev: precision-math-decimal-characteristics, Up: precision-math
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 note 'INSERT': insert. into a column with an exact data type (note 'DECIMAL': fixed-point-types. 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. (If strict SQL mode is not enabled, truncation for *note 'INSERT': insert. is permissible.)
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 note 'DECIMAL': fixed-point-types. 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 *note 'BIGINT': integer-types. (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 *note sql-mode::.) 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':
SET sql_mode='TRADITIONAL';If a number is inserted into an exact type column (*note 'DECIMAL': fixed-point-types. or integer), it is inserted with its exact value if it is within the column range and precision.
If the value has too many digits in the fractional part, rounding occurs and a note is generated. Rounding is done as described in *note precision-math-rounding::. Truncation due to rounding of the fractional part is not an error, even in strict mode.
If the value has too many digits in the integer part, it is too large (out of range) 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.
For *note 'DECIMAL': fixed-point-types. literals, in addition to the precision limit of 65 digits, there is a limit on how long the text of the literal can be. If the value exceeds approximately 80 characters, unexpected results can occur. For example:
mysql> SELECT
CAST(0000000000000000000000000000000000000000000000000000000000000000000000000000000020.01 AS DECIMAL(15,2)) as val;
+------------------+
| val |
+------------------+
| 9999999999999.99 |
+------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '20' |
| Warning | 1264 | Out of range value for column 'val' at row 1 |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)Underflow is not detected, so underflow handling is undefined.
For inserts of strings into numeric columns, conversion from string to number is handled as follows if the string has nonnumeric 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 nonnumeric portion is truncated. If the truncated portion contains anything other than spaces, this produces an error in strict mode, or a warning otherwise.
By default, division by zero produces a result of 'NULL' and no warning. By setting the SQL mode appropriately, division by zero can be restricted.
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.
File: manual.info.tmp, Node: precision-math-rounding, Next: precision-math-examples, Prev: precision-math-expressions, Up: precision-math
This section discusses precision math rounding for the 'ROUND()' function and for inserts into columns with exact-value types (*note 'DECIMAL': fixed-point-types. 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. (In other words, it is rounded toward zero.)
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 a fractional part exactly half way between two integers is rounded to the nearest even integer.
The following example shows how rounding differs for exact and approximate values:
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3 | 2 |
+------------+--------------+For inserts into a *note 'DECIMAL': fixed-point-types. or integer column, the target is an exact data type, so rounding uses 'round half away from zero,' regardless of whether the value to be inserted is exact or approximate:
mysql> CREATE TABLE t (d DECIMAL(10,0));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES(2.5),(2.5E0);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------+
| Note | 1265 | Data truncated for column 'd' at row 1 |
| Note | 1265 | Data truncated for column 'd' at row 2 |
+-------+------+----------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT d FROM t;
+------+
| d |
+------+
| 3 |
| 3 |
+------+
2 rows in set (0.00 sec)The note 'SHOW WARNINGS': show-warnings. statement displays the notes that are generated by truncation due to rounding of the fractional part. Such truncation is not an error, even in strict SQL mode (see note precision-math-expressions::).
File: manual.info.tmp, Node: precision-math-examples, Prev: precision-math-rounding, Up: precision-math
This section provides some examples that show precision math query results in MySQL. These examples demonstrate the principles described in note precision-math-expressions::, and note precision-math-rounding::.
Example 1. Numbers are used with their exact value as given when possible:
mysql> SELECT (.1 + .2) = .3;
+----------------+
| (.1 + .2) = .3 |
+----------------+
| 1 |
+----------------+For floating-point values, results are inexact:
mysql> SELECT (.1E0 + .2E0) = .3E0;
+----------------------+
| (.1E0 + .2E0) = .3E0 |
+----------------------+
| 0 |
+----------------------+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 that have scale S1 and S2, the scale of the result is 'S1 + S2':
mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001 |
+-----------+Example 3. Rounding behavior for exact-value numbers 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 (*note 'DECIMAL': fixed-point-types. and integer) and exact-valued numbers uses the 'round half away from zero' rule. A value with a fractional part of .5 or greater is rounded away from zero to the nearest integer, as shown here:
mysql> SELECT ROUND(2.5), ROUND(-2.5);
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3 | -3 |
+------------+-------------+Rounding for floating-point values uses the C library, which on many systems uses the 'round to nearest even' rule. A value with a fractional part exactly half way between two integers is rounded to the nearest even integer:
mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0);
+--------------+---------------+
| ROUND(2.5E0) | ROUND(-2.5E0) |
+--------------+---------------+
| 2 | -2 |
+--------------+---------------+Example 4. In strict mode, inserting a value that is out of range for a column 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> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t SET i = 128;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT i FROM t;
+------+
| i |
+------+
| 127 |
+------+
1 row in set (0.00 sec)However, an error occurs if strict mode is in effect:
mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = 128;
ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1
mysql> SELECT i FROM t;
Empty set (0.00 sec)Example 5: In strict mode and with 'ERROR_FOR_DIVISION_BY_ZERO' set, division by zero causes an error, not a result of 'NULL'.
In nonstrict mode, division by zero has a result of 'NULL':
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = 1 / 0;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT i FROM t;
+------+
| i |
+------+
| NULL |
+------+
1 row in set (0.03 sec)However, division by zero is an error if the proper SQL modes are in effect:
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);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = 1 / 0;
ERROR 1365 (22012): Division by 0
mysql> SELECT i FROM t;
Empty set (0.01 sec)Example 6. Exact-value literals are evaluated as exact values.
Approximate-value literals are evaluated using floating point, but exact-value literals are handled as *note 'DECIMAL': fixed-point-types.:
mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> DESCRIBE t;
+-------+-----------------------+------+-----+---------+-------+
| 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)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> CREATE TABLE t (i INT, d DECIMAL, f FLOAT);
mysql> INSERT INTO t VALUES(1,1,1);
mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;The result is a double only for the floating-point argument. For exact type arguments, the result is also an exact type:
mysql> DESCRIBE y;
+--------+---------------+------+-----+---------+-------+
| 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 | |
+--------+---------------+------+-----+---------+-------+The result is a double only for the floating-point argument. For exact type arguments, the result is also an exact type.
File: manual.info.tmp, Node: sql-statements, Next: innodb-storage-engine, Prev: functions, Up: Top
13 SQL Statements *****************
Menu:
sql-utility-statements:: Utility Statements
This chapter describes the syntax for the SQL statements supported by MySQL.
File: manual.info.tmp, Node: sql-data-definition-statements, Next: sql-data-manipulation-statements, Prev: sql-statements, Up: sql-statements