(mysql.info) precision-math-expressions

Info Catalog

(mysql.info) precision-math-decimal-changes

(mysql.info) precision-math

(mysql.info) precision-math-rounding

 
 21.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 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 
 server-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':
 
      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
 `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 non-numeric 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 non-numeric portion is truncated. If the truncated
      portion contains anything other than spaces, this produces an
      error in strict mode, or a warning otherwise.
 

Info Catalog

(mysql.info) precision-math-decimal-changes

(mysql.info) precision-math

(mysql.info) precision-math-rounding