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.8, “Server 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 11.14.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 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.
User Comments
Add your own comment.