![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961024.webp)
Expression Handling
1004
• Leading “
+
” and “
0
” characters are not stored.
The behavior used by the server for
DECIMAL
columns in a table depends on the version of MySQL
used to create the table. If your server is from MySQL 5.0.3 or higher, but a table that was created
before 5.0.3 has
DECIMAL
columns, the old behavior still applies to those columns. To convert the
table to the newer
DECIMAL
format, dump it with
mysqldump
and reload it.
12.17.3. Expression Handling
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 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
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
[495]
system variable. (See
Section 5.1.7, “Server SQL Modes”
.) The following discussion mentions
strict mode (selected by the
STRICT_ALL_TABLES
[539]
or
STRICT_TRANS_TABLES
[539]
mode values) and
ERROR_FOR_DIVISION_BY_ZERO
[536]
. To turn on all restrictions, you
can simply use
TRADITIONAL
[540]
mode, which includes both strict mode values and
ERROR_FOR_DIVISION_BY_ZERO
[536]
:
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 12.17.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 handling is undefined.
By default, division by zero produces a result of
NULL
and no warning. With the
ERROR_FOR_DIVISION_BY_ZERO
[536]
SQL mode enabled, MySQL handles division by zero
differently:
• If strict mode is not enabled, a warning occurs.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...