• notice
  • Congratulations on the launch of the Sought Tech site

How to handle illegal data in mysql

By default, MySQL handles "data out of bounds" and other abnormal data according to the following rules

1.For numeric data or time data columns, values ​​beyond the legal range will be phased to the nearest value range boundary;

2.Strings that are too long for string data columns (excluding enum, set) will be truncated to the maximum length of the data column.For

  For ENUM type data, MySQL replaces illegal members with empty strings.If the assignment to a set data column contains illegal substrings, mysql will delete those substrings and put The rest is assigned to the data column

3.For date and time data columns, illegal values ​​will be converted to '0' values ​​instead

When the above situation occurs, MySQL will generate a warning, which can be viewed using show warnings.

4.For string sequences, too long strings will be truncated to the maximum length of the data column

If stricter checking is required when inserting or updating data, a special SQL MODE can be enabled;


For tables that support transactions, the two modes are the same: if a value is found to be missing or invalid, MySQL will throw an error and the statement will stop and roll back.

For tables that do not support transactions, the effects of these two modes:

1.If a value is found to be illegal or missing when inserting or modifying the first data row, the statement will throw an error and stop executing the statement.This is the same as the behavior of data tables that support transactions.

2.If an error is found when inserting or modifying the nth (n>1) data row, the following will occur:

2.1 In the strict_all_tables mode, the execution of the statement is stopped, and there is a problem of partial update

2.2 In strict_trans_tables mode, MySQL will continue to execute the statement to avoid the "partial update problem", converting each illegal value to the nearest legal value.

Several other SQL modes that work with strict mode:

ERROR_FOR_DIVISION_BY_ZERO: In strict mode, if it encounters a divisor of 0, it refuses to insert into the database.(If it is not in strict mode, MYSQL will generate a warning and insert a NULL value)

NO_ZERO_DATE: In strict mode, reject 0 date values ​​from entering the database.

Recommended: strict_trans_tables ERROR_FOR_DIVISION_BY_ZERO

Another way to suppress errors is to use the IGNORE keyword in an insert or update statement to reduce an error caused by an invalid value to a warning.

This article is from the blog "Technology Achieves Dreams", please keep this source http://weipengfei.blog.51cto.com/1511707/1173816


Technical otaku

Sought technology together

Related Topic


Leave a Reply