How to handle illegal data in MySQL

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

1. For numeric data or time data columns, values exceeding 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) are truncated to the maximum length of the data column.

Enum type data, MySQL replaces an invalid member with an empty string. If an assignment to a set data column contains an illegal substring, MySQL deletes those substrings and assigns the remainder to the data column

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

When this happens, MySQL generates a warning that can be viewed using show warnings.

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

You can enable special SQL MODE if you need to check more rigorously when inserting or updating data.

Sql_mode= ' Strict_all_tables,strict_trans_tables '

For tables that support transactions, the two patterns are the same: If a value is found to be missing or illegal, MySQL throws an error and the statement stops running and rolls back.

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

1. If a value is found to be illegal or missing when inserting or repairing the first data row, the statement is directly misplaced and the statement stops executing. This is the same as data table behavior that supports transactions.

2. If an error is found while inserting or modifying the nth (n>1) data row, the following situation occurs:

2.1 In Strict_all_tables mode, stop statement execution, there are some problems with the update

2.2 In Strict_trans_tables mode, MySQL will continue to execute the statement to avoid "partial update issues", converting it to the nearest legal value for each illegal value.

Several other SQL modes in conjunction with strict mode:

Error_for_division_by_zero: In strict mode, encountered in the case of 0 divisor, refused to insert the database, (if not in strict mode, MySQL will generate an alarm, and insert a null value)

No_zero_date: In strict mode, reject the 0 days period value into the database.

Recommended: Strict_trans_tables Error_for_division_by_zero

Another way to stop the error is to use the Ignore keyword in the INSERT or UPDATE statement to weaken the error caused by the illegal value to a warning.

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1173816

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/