Constraints on MySQL Invalid data

Before MySQL 5.0.2, MySQL was not strict about illegal or improper values, and it also forced them into legal values for data entry. In the MySQL 5.0.2 and later versions, the previous default behavior is retained, but you can choose a more traditional approach for bad values, allowing the server to reject and discard statements that appear to have bad values. This section describes the default behavior of MySQL (lenient behavior), the new strict SQL schema, and their differences.

If you do not use strict mode, the following conditions are true. If you insert an incorrect value into a column, such as inserting a null value into a non-null column, or inserting an too large value into a numeric column, MySQL sets the columns to the most probable value instead of generating an error message.

· If you attempt to save a range of values to a numeric column, the MySQL server will save 0 (the smallest possible value) instead, or the maximum possible value.

· For strings, MySQL either saves an empty string, or saves as many portions of the string as possible to the column.

· If you intend to save a string that is not at the beginning of a value to a numeric column, MySQL will save 0.

· MySQL allows specific incorrect date values to be saved to date and datetime columns (such as "2000-02-31" or "2000-02-00"). The idea is that the validation date is not the task of the SQL Server. If MySQL can save the date value and retrieve exactly the same value, MySQL can save it at the given value. If the date is completely incorrect (beyond the range that the server can save), the special date value "0000-00-00" is saved in the column instead.

· If you attempt to save a null value to a column that does not accept null values, an error occurs for a single row insert statement. For multiple-line INSERT statements or INSERT INTO ... Select statement, the MySQL server saves an implied default value for the column data type. Typically, for a numeric type, it is 0, and for a string type, it is an empty string (""), which is "zero" for the date and time type.

· If the INSERT statement does not specify a value for the column, MySQL inserts the default value if the column definition contains an explicit default clause. If there is no such default clause in the definition, MySQL inserts the implied default value of the column data type.

The reason for using the preceding rule is that it is not possible to check these conditions until the statement starts executing. If you encounter such problems after updating a number of lines, we cannot only resolve them by rolling back, because the storage engine may not support rollback. The abort statement is not a good choice, in which case the update completes "half", which may be the worst case scenario. For this example, a better approach would be to "just do the best" and then continue as if nothing had happened.

In the MySQL 5.0.2 and later versions, you can use the strict_trans_tables or Strict_all_tables SQL mode to choose a more rigorous approach.

The way Strict_trans_tables works:

· For transactional storage engines, bad data values that appear anywhere in the statement cause the statement to be discarded and a rollback is performed.

· For a non-transactional storage engine, the statement is discarded if the error appears at line 1th that is now being inserted or updated. (In this case, you can assume that the statement does not alter the table, just like a transaction table). Errors that occur after the first row do not result in a discard statement. Instead, the bad data values are adjusted and alarms are given, not errors. In other words, if you use Strict_trans_tables, the error value causes MySQL to perform a rollback operation, and if you can, all updates end here.

To perform a more rigorous check, enable strict_all_tables. In addition to the Non-transactional storage engine, it is equivalent to strict_trans_tables, even when bad data appears on other lines after the first row, and the resulting error causes the discard statement. This means that if the error occurs halfway through the multiple-row insert or update process of a non-transactional table, only partial results are updated. The preceding row completes the insert or update, but the line following the error appears otherwise. For non-transactional tables, to avoid this, use a single-line statement, or use Strict_trans_tables if you can accept a conversion warning instead of an error. To prevent problems from appearing in the 1th match, do not use MySQL to check the contents of the columns. The safest way (and often faster) is to have the application take responsibility for passing only valid values to the database.

With the strict mode option, you can use the Insert ignore or update ignore instead of INSERT or update without ignore to treat the error as an alarm.