column, NULL  is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE . 

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings (see Section 13.7.7.42, “SHOW WARNINGS Statement” ). In strict mode, you can produce this behavior by using INSERT IGNORE  or UPDATE IGNORE . 

For statements such as SELECT  that do not change data, invalid values generate a warning in strict mode, not an error. 

Strict mode produces an error for attempts to create a key that exceeds the maximum key length. When strict mode is not enabled, this results in a warning and truncation of the key to the maximum key length. 

Strict mode does not affect whether foreign key constraints are checked. foreign_key_checks  can be used for that. (See Section 5.1.8, “Server System Variables” .) 

Strict SQL mode is in effect if either STRICT_ALL_TABLES  or STRICT_TRANS_TABLES  is enabled, although the effects of these modes differ somewhat: 

•For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES  or STRICT_TRANS_TABLES  is enabled. The statement is aborted and rolled back. 

•For nontransactional tables, the behavior is the same for either mode if the bad value occurs in the first row to be inserted or updated: The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict mode is enabled: 

•For STRICT_ALL_TABLES , MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table. 

•For STRICT_TRANS_TABLES , MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.6, “Data Type Default Values” . 

Strict mode affects handling of division by zero, zero dates, and zeros in dates as follows: 

•Strict mode affects handling of division by zero, which includes MOD( N ,0) : 

For data-change operations ( INSERT , UPDATE ): 

•If strict mode is not enabled, division by zero inserts NULL  and produces no warning. 

•If strict mode is enabled, division by zero produces an error, unless IGNORE  is given as well. For INSERT IGNORE  and UPDATE IGNORE , division by zero inserts NULL  and produces a warning. 

For SELECT , division by zero returns NULL . Enabling strict mode causes a warning to be produced as well. 

•Strict mode affects whether the server permits ’0000-00-00’  as a valid date: 

•If strict mode is not enabled, ’0000-00-00’  is permitted and inserts produce no warning. 

