Movatterモバイル変換

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, forINSERT 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. (If strict SQL mode is not enabled, truncation forINSERT is permissible.)

Handling of a numeric expression depends on what kind of values the expression contains:

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 thesql_mode system variable. (SeeSection 7.1.11, “Server SQL Modes”.) The following discussion mentions strict mode (selected by theSTRICT_ALL_TABLES orSTRICT_TRANS_TABLES mode values) andERROR_FOR_DIVISION_BY_ZERO. To turn on all restrictions, you can simply useTRADITIONAL mode, which includes both strict mode values andERROR_FOR_DIVISION_BY_ZERO:

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 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 inSection 14.25.4, “Rounding Behavior”. 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:

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:

By default, division by zero produces a result ofNULL and no warning. By setting the SQL mode appropriately, division by zero can be restricted.

With theERROR_FOR_DIVISION_BY_ZERO SQL mode enabled, MySQL handles division by zero differently:

In other words, inserts and updates involving expressions that perform division by zero can be treated as errors, but this requiresERROR_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 andERROR_FOR_DIVISION_BY_ZERO modes.