Restrictions on invalid data in mysql

Invalid Data in mysql is restricted before MySQL 5.0.2. mysql is not strict with illegal or improper values, and it forcibly changes them to valid values for data input. In MySQL5.0.2 and later versions, the default behavior is retained, but you can select a more traditional processing method for the negative value, so that the server can reject and discard statements with negative values. Www.2cto.com This section describes the default MySQL behavior (loose behavior), the new strict SQL mode, and their differences. If you do not use the strict mode, the following situations are true. If you insert an "Incorrect" value into a column, such as inserting a NULL value into a non-NULL column or inserting a large value into a value column, mySQL sets these columns as "the most possible values" instead of generating error messages. If you try to save the out-of-range value to the value column, the MySQL server will save 0 (minimum possible value) instead, or the maximum possible value. For a string, MySQL either saves an empty string or saves as many parts of the string as possible to the column. If you want to save a string that does not start with a value to the value column, MySQL will save 0. MySQL allows you to save specific incorrect DATE values to the DATE and DATETIME columns (for example, "2000-02-31" or "2000-02-00 "). The view is that the verification date is not a task of the SQL Server. If MySQL can save the date value and accurately retrieve the same value, MySQL can save it based on the given value. If the date is completely incorrect (beyond the range that the server can save), the special date value "" will be saved in the column. If you try to save the NULL value to a column that does not accept the NULL value, an error will occur for the single row INSERT statement. For multi-row INSERT statements or INSERTINTO... SELECT statements, the MySQL server will save the implicit default values for column data types. Generally, for the value type, it is 0, for the string type, it is a null string (''), for the date and time type is" zero ". If the INSERT statement does not specify a value for the column, MySQL inserts the DEFAULT value if the column definition contains a clear DEFAULT clause. If such a DEFAULT clause is not included in the definition, MySQL inserts an implicit DEFAULT value for the column data type. The reason for using the preceding rules is that these conditions cannot be checked before the statement is executed. If this type of problem occurs after several rows are updated, we cannot solve it by rolling back. This is because the storage engine may not support rollback. The abort statement is not a good choice. In this case, the update is completed in half, which may be the worst case. In this example, the better method is "only possible to do the best", and then continue as nothing happens. In MySQL5.0.2 and later versions, www.2cto.com can use the STRICT_TRANS_TABLES or STRICT_ALL_TABLESSQL mode and select a more strict processing method. STRICT_TRANS_TABLES: for a transaction storage engine, a bad data value anywhere in the statement will cause the statement to be abandoned and rolled back. For non-transactional storage engines, if the error occurs on the 1st rows to be inserted or updated, the statement is discarded. (In this case, it can be considered that the statement has not changed the table, just like the transaction table ). The error after the first line does not cause the statement to be abandoned. Instead, it will adjust the bad data value and give an alarm instead of an error. In other words, after STRICT_TRANS_TABLES is used, the error value will cause MySQL to perform the rollback operation. If yes, all updates will end here. To perform more rigorous checks, enable STRICT_ALL_TABLES. In addition to the non-transactional storage engine, it is equivalent to STRICT_TRANS_TABLES. Even if bad data appears in other rows after the first row, the generated error will cause the statement to be abandoned. This means that if the error occurs halfway through the multi-row insertion or update process of a non-transactional table, only partial results are updated. The previous row is inserted or updated, but the row after the dot is incorrect. For non-transactional tables, to avoid this situation, you can use a single row statement, or use STRICT_TRANS_TABLES when the conversion warning is acceptable rather than an error. To prevent problems in 1st, do not use MySQL to check the column content. The safest way (usually faster) is to let the application take responsibility and pass only valid values to the database. With strict mode options, you can use INSERTIGNORE or UPDATEIGNORE instead of INSERT or UPDATE without IGNORE to treat errors as warnings. This article is from Yulong net.