Considerations for mysql INTEGER (int) numeric overflow in programming and database design

The range of numbers must be considered in database design and procedures; otherwise, some problems may occur. The main consideration is overflow. For example, if the database has an integer numeric field, the data in it may expand as the business grows, and this number may be out of the column attribute range, that is, overflow, at the same time, the program also needs to deal with this increasingly

The range of numbers must be considered in database design and procedures; otherwise, some problems may occur. The main consideration is overflow. For example, if the database has an integer numeric field, the data in it may expand as the business grows, and this number may be out of the column attribute range, that is, overflow, at the same time, the program also needs to deal with this increasingly

The following example uses an integer (int) as an example:

I. MySQL5

Taking MySQL 5 as an example, most administrators may set the column attribute of auto-increment numbers or other numeric fields to int type. int occupies 4 bytes, the int type can be unsigned and signed. The range of the unsigned type is 0 to 4294967295; the range of the signed type is-2147483648 to 2147483647. See mysql manual for reference: 11.2. value type.

When you want to save a value that exceeds the permitted range of the column in a numeric column, the MySQL operation depends on the valid SQL mode. If the mode is not set, MySQL crops the value to the corresponding endpoint of the range and saves the cut value. However, if the mode is set to traditional ("strict mode"), values out of the range will be rejected and an error will be prompted, and insertion based on SQL standards will fail. See section 5.3.2 of mysql Manual: "SQL SERVER mode ".

If the INT column is UNSIGNED, the column range is the same, but its endpoint is changed to 0 and 4294967295. If you try to save-9999999999 and 9999999999, the values saved to the column in non-strict mode are 0 and 4294967296.

If the value allocated in the floating point or fixed point column exceeds the specified (or default) precision and scale range, MySQL saves the value of the corresponding range endpoint in non-strict mode.

When MySQL does not work in strict mode, the conversion of alter table, load data infile, UPDATE, and Multiline INSERT statements due to cropping will be reported as a warning. When MySQL works in strict mode, these statements fail, and some or all values are not inserted or changed, depending on whether the table is a transaction table and other factors. For more information, see section 5.3.2 "SQL SERVER mode" in the mysql manual ".
Ii. php5:

1: the length of the integer is related to the platform. PHP does not support unsigned integers.
2: if a given number exceeds the integer Range, it will be interpreted as float. Similarly, if the execution result exceeds the integer Range, float is returned. If you compare the numeric types in the program, problems may occur.
3: You can view PHP_INT_SIZE and PHP_INT_MAX to determine the Integer Range.

The following columns are for reference:

On a 32-bit Server:

The following is a code snippet:

The Code is as follows:

[Shengting @ localhost ~] $ Php-r "echo PHP_INT_SIZE ;" 4 [Shengting @ localhost ~] $ Php-r "echo PHP_INT_MAX ;" 2147483647 [Shengting @ localhost ~] $ Php-r "var_dump (2147483647 );" Int (2147483647) [Shengting @ localhost ~] $ Php-r "var_dump (2147483648 );" Float (2147483648) [Shengting @ localhost ~] $ Php-r "var_dump (-2147483647 );" Int (-1, 2147483647) [Shengting @ localhost ~] $ Php-r "var_dump (-2147483648 );" Float (-1, 2147483648) [Shengting @ localhost ~] $ Php-r "var_dump (4294967295 );" Float (4294967295) [Shengting @ localhost ~] $ Php-r "var_dump (4294967296 );" Float (4294967296)

On a 64-bit Server:

The following is a code snippet:

The Code is as follows:

[Root @ login shengting] # php-r "echo PHP_INT_SIZE ;" 8 [Root @ login shengting] # php-r "echo PHP_INT_MAX ;" 9223372036854775807 [Root @ login shengting] # php-r "var_dump (2147483647 );" Int (2147483647) [Root @ login shengting] # php-r "var_dump (2147483648 );" Int (2147483648) [Root @ login shengting] # php-r "var_dump (-2147483647 );" Int (-1, 2147483647) [Root @ login shengting] # php-r "var_dump (-2147483648 );" Int (-1, 2147483648) [Root @ login shengting] # php-r "var_dump (4294967295 );" Int (4294967295) [Root @ login shengting] # php-r "var_dump (4294967296 );" Int (4294967296)

III. C/C ++

C/C ++ also has the problem of signed and unsigned types.

For 32-bit systems, if you use the signed int or long to define the variable to save the unique number, overflow may occur and the above problem occurs.

For 64-bit systems, overflow may occur if int is used to define the variable to save the unique number.