Now, let me 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 '0', n2 INT(11) NOT NULL DEFAULT '0' ); Then we use the following statement to insert some data into the na table mysql> INSERT INTO `na` VALUES(520,520),(5201314,5201314); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 Finally, let’s read it out and see mysql> SELECT * FROM na; +---------+---------+ | n1 | n2 | +---------+---------+ | 520 | 520 | | 5201314 | 5201314 | +---------+---------+ 2 rows in set (0.00 sec) Right, it seems like nothing will happen, it’s right if there is no problem, I’m just afraid if there is any problem…haha In this chapter we will talk about integer overflow problems. MySQL numeric type overflow processing When MySQL stores a value in a numeric column that is outside the range allowed by the column data type, the result depends on the SQL mode in effect at the time.
This should be easy to understand, right? Let's take an example, assuming that the structure of table t1 is as follows CREATE TABLE t1 ( i1 TINYINT, i2 TINYINT UNSIGNED ); If strict SQL mode is enabled, an error will occur if the range is exceeded. mysql> SET sql_mode = 'TRADITIONAL'; -- First set strict mode mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256); ERROR 1264 (22003): Out of range value for column 'i1' at row 1 mysql> SELECT * FROM t1; Empty set (0.00 sec) When strict mode is disabled, values can be inserted, but they are clipped 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, statements such as ALTER TABLE, LOAD DATA INFILE, UPDATE, and multi-row INSERT will cause column assignment conversions due to pruning and raise a warning. If strict mode is enabled, these statements simply fail and some or all values are not inserted or changed, depending on whether the table is transactional and other factors. Overflow during the evaluation of a numeric expression results in an error. For example, because the largest signed BIGINT value is 9223372036854775807, the following expression results in an error. mysql> SELECT 9223372036854775807 + 1; ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)' In order for the operation to succeed in this case, the value needs to be converted to unsigned mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1; +-------------------------------------------+ | CAST(9223372036854775807 AS UNSIGNED) + 1 | +-------------------------------------------+ |9223372036854775808 | +-------------------------------------------+ On the other hand, whether overflow occurs depends on the range of the operands, so another way to process the previous expression is to use exact-value arithmetic, since the range of DECIMAL values is greater than that of integers. mysql> SELECT 9223372036854775807.0 + 1; +---------------------------+ | 9223372036854775807.0 + 1 | +---------------------------+ |9223372036854775808.0 | +---------------------------+ 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.00 sec) mysql> SELECT CAST(0 AS UNSIGNED) - 1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)' In this case, if the 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 an UNSIGNED integer column, the result is clipped to the maximum value for the column type, or to 0 if NO_UNSIGNED_SUBTRACTION is enabled. However, if strict SQL mode is enabled, an error occurs and the column remains unchanged. postscript Everything is a routine, routine....basically related to SQL mode... Summarize The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. You may also be interested in:
|
<<: Node.js+postman to simulate HTTP server and client interaction
>>: Docker image export, import and copy example analysis
Use auto.js to automate daily check-in Due to the...
In this article, I will show you how to install a...
To merge the following two files, merge them toge...
Adding the extra_hosts keyword in docker-compose....
The <tfoot> tag is used to define the style...
Table of contents js deep copy Data storage metho...
The web pinball game implemented using javeScript...
The most common mistake made by many website desi...
Table of contents Explanation of v-text on if for...
Method 1: Use the lsb_release utility The lsb_rel...
The reuse of code in vue provides us with mixnis....
Today I happened to be helping a friend move his ...
Configure the accelerator for the Docker daemon S...
Table of contents Overview Hash Properties Host p...
Today we will introduce several ways to use CSS t...