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

Come on, let's ask you a question: what happens when a column is set to int (0) in MySQL?

To demonstrate this problem, we first create a table

DROP TABLE IF EXISTS `na`; CREATE TABLE `na` (N1 INT (0) NOT NULL DEFAULT '0writing N2 INT (11) NOT NULL DEFAULT' 0')

Then we insert some data into the na table using the following statement

Mysql > INSERT INTO `na` VALUES (520520), (5201314 sec 5201314); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0

Finally, let's read it out and take a look.

Mysql > SELECT * FROM na;+-+-+ | N1 | N2 | +-+-+ | 5201314 | 5201314 | +-+-+ 2 rows in set (0.00 sec)

Yes, it seems that nothing will happen, no problem is right, I am afraid that there is a problem. Ha ha

We will talk about the problem of integer overflow in this chapter.

MySQL numeric type overflow handling

When MySQL stores values on a numeric column that are outside the range allowed by the column data type, the result depends on the SQL schema that is in effect at that time

If strict SQL mode is enabled, MySQL rejects out-of-range values with errors according to the SQL standard, and insert fails if no restricted mode is enabled, then MySQL will crop the values to the upper and lower limits of the column data type range and store when out-of-range values are assigned to integer columns MySQL stores values representing the corresponding endpoints of a range of column data types. When a value assigned to a floating-point or fixed-point column exceeds the range implied by the specified (or default) precision and scale, MySQL stores a value that represents the corresponding endpoint of the range.

Well, it should be easy to understand, right?

Let's take an example and assume that the structure of the T1 table is as follows

CREATE TABLE T1 (i1 TINYINT,i2 TINYINT UNSIGNED)

If strict SQL mode is enabled, an error occurs when it is out of range

Mysql > SET sql_mode = 'TRADITIONAL';-- first set strict mode mysql > INSERT INTO T1 (i1, i2) VALUES (256256); ERROR 1264 (22003): Out of range value for column 'i1' at row 1mysql > SELECT * FROM T1 emptiness set (0.00 sec)

When strict mode is disabled, the value can be inserted, but it is cropped and a warning is raised

Mysql > SET sql_mode ='';-- disable all modes mysql > INSERT INTO T1 (i1, i2) VALUES (256,256); mysql > SHOW WARNINGS +-+ | Level | Code | Message | +- -+ | Warning | 1264 | Out of range value for column 'i1' at row 1 | | Warning | 1264 | Out of range value for column 'i2' at row 1 | +-+ mysql > SELECT * FROM T1 +-+ | i1 | i2 | +-+-+ | 127 | 255 | +-+-+

If strict SQL mode is not enabled, column allocation transformations that occur due to tailoring and raise a warning for statements such as ALTER TABLE,LOAD DATA INFILE,UPDATE and multiline INSERT.

If strict mode is enabled, these statements fail directly and some or all of the values are not inserted or changed, depending on whether the table is a transaction table and other factors.

An overflow in the evaluation of a numeric expression can result in an error, for example, because the maximum signed BIGINT value is 9223372036854775807

Mysql > SELECT 9223372036854775807 + 1 position error 1690 (22003): BIGINT value is out of range in'(9223372036854775807 + 1)'

In order for the operation to succeed in this case, you need to convert the value to unsigned

Mysql > SELECT CAST (9223372036854775807 AS UNSIGNED) + 1 +-- + | CAST (9223372036854775807 AS UNSIGNED) + 1 | +-+ | 9223372036854775808 | +- -- +

On the other hand, whether an overflow occurs depends on the range of operands, so another way to deal with the previous expression is to use exact arithmetic, because the range of DECIMAL values is larger than that of integers

Mysql > SELECT 9223372036854775807.0 + 1 | 9223372036854775807.0 + 1 | +-+ | 9223372036854775808.0 | +-+

The subtraction between integer values, if one of the types is UNSIGNED, produces an unsigned result by default. If negative, an error is raised

Mysql > SET sql_mode =''; Query OK, 0 rows affected (0 sec) mysql > SELECT CAST (0 AS UNSIGNED)-1 cast error 1690 (22003): BIGINT UNSIGNED value is out of range in'(0 as unsigned)-1)'

In this case, if NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative

Mysql > SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';mysql > SELECT CAST (0 AS UNSIGNED)-1 + | CAST (0 AS UNSIGNED)-1 | +-+ |-1 | +-+

If the result of such an operation is used to update the UNSIGNED integer column, the result is cropped to the maximum value of the column type, or 0 if NO_UNSIGNED_SUBTRACTION is enabled. However, if strict SQL mode is enabled, an error occurs and the column remains the same.

Postscript

Everything is a routine, a routine... . Basically it has to do with SQL mode.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.