MySQL data out of range processing

MySQL by default, when a data exceeds the range of the data type that defines the column, the data is stored with the maximum value that is allowed for the data type.

Cases:

1. Create a table

CREATE TABLE t1 (' num ' int not null);

2. Inserting data

INSERT INTO T1 (' num ') value (2147483648);

[SQL] INSERT into T1 (num) value (2147483648); affected line: 1 time: 0.003s

3. Enquiry

SELECT * from T1;

+------------+| Num |+------------+| 2147483647 |+------------+1 row in Set (0.00 sec)

Here you can see that the data in the table is 2147483647, not the value we want to see, in which case MySQL inserts data without an error or warning.

In a real-world scenario, if the word Gencun is a very sensitive number, such as bank deposits, money-related data, this is not the result we want to get.

In this case, we want to insert or update the field if the inserted or updated value exceeds the range of the defined field data type, an error is raised, the modification data is forbidden by modifying the global variable Sq_mode为STRICT_ALL_TABLES实现。


在my.cnf或my.ini中添加sql_mode=STRICT_ALL_TABLES，重启mysql_server即可。


例：


insert into t1(`num`) value(2147483648);

[SQL] INSERT INTO T1 (' num ') value (2147483648); [ERR] 1054-unknown column ' num ' in ' field list '

This article is from the "lang8027" blog, make sure to keep this source http://lang8027.blog.51cto.com/9606148/1964527

MySQL data out of range processing