Rounding Behavior
1005
• 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
[536]
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
[536]
modes.
sql_mode
[495]
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
[536]
Warning, no error;
i
is set to
NULL
.
strict,
ERROR_FOR_DIVISION_BY_ZERO
[536]
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.
12.17.4. Rounding Behavior
This section discusses precision math rounding for the
ROUND()
[913]
function and for inserts into
columns with exact-value types (
DECIMAL
and integer).
The
ROUND()
[913]
function rounds differently depending on whether its argument is exact or
approximate:
• For exact-value numbers,
ROUND()
[913]
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()
[913]
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>
SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3 | 2 |
+------------+--------------+
For inserts into a
DECIMAL
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);
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
