Shulou(Shulou.com)06/01 Report--

Mysql operation mode and 1690 error handling MySQL said: Documentation

1690-BIGINT UNSIGNED value is out of range in

After query, it is found that the reason for this error is that when two time fields are performing subtraction, if one time is 0000-00-00:00, the fundamental reason is that the result of such subtraction will exceed the range of the Mysql numeric field, thus triggering 1690 error.

ERROR 1690 Out-of-Range

A 1690 Out of Range error is triggered when a number field in Mysql stores a number that exceeds the allowable range, depending on the mode of the SQL runtime:

Data insertion fails when standard Standar Mode or Strict Mode is running

When unrestricted mode No Restrictive is running, Mysql converts the value to the maximum or minimum value allowed in the range for storage

Solution method

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'

In the calculation, first execute the above statement, you can avoid errors in the process of subtraction. In the Mysql document, it is clearly stated that the result of subtraction between two integers is an unsigned number, and before Mysql 5.5.5, if a negative number is generated, mysql converts the number to the largest number.

Since Mysql 5.5.5, if a negative number is generated, an error ERROR 1690 will be generated.

About SQL_MODE

SQL_ mode is empty by default, and there are many options. It is recommended to set it to strict MODE in the production environment, so that a lot of trouble can be avoided during operation and maintenance.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.