Skip navigation links
1.9.6.3 ENUM and SET Constraints »
Section Navigation      [Toggle]

1.9.6.2. Constraints on Invalid Data

Through version 4.1, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. When you insert an “incorrect” value into a column, such as a NULL into a NOT NULL column or a too-large numeric value into a numeric column, MySQL sets the column to the “best possible value” instead of producing an error. The following rules describe in more detail how this works:

  • If you try to store an out of range value into a numeric column, MySQL Server instead stores zero, the smallest possible value, or the largest possible value, whichever is closest to the invalid value.

  • For strings, MySQL stores either the empty string or as much of the string as can be stored in the column.

  • If you try to store a string that doesn't start with a number into a numeric column, MySQL Server stores 0.

  • Invalid values for ENUM and SET columns are handled as described in Section 1.9.6.3, “ENUM and SET Constraints”.

  • MySQL allows you to store certain incorrect date values into DATE and DATETIME columns (such as '2000-02-31' or '2000-02-00'). The idea is that it is not the job of the SQL server to validate dates. If MySQL can store a date value and retrieve exactly the same value, MySQL stores it as given. If the date is totally wrong (outside the server's ability to store it), the special “zero” date value '0000-00-00' is stored in the column instead.

  • If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT statements. For multiple-row INSERT statements or for INSERT INTO ... SELECT statements, MySQL Server stores the implicit default value for the column data type. In general, this is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. Implicit default values are discussed in Section 10.1.4, “Data Type Default Values”.

  • If an INSERT statement specifies no value for a column, MySQL inserts its default value if the column definition includes an explicit DEFAULT clause. If the definition has no such DEFAULT clause, MySQL inserts the implicit default value for the column data type.

The reason for using the preceding rules is that we can't check these conditions until the statement has begun executing. We can't just roll back if we encounter a problem after updating a few rows, because the storage engine may not support rollback. The option of terminating the statement is not that good; in this case, the update would be “half done,” which is probably the worst possible scenario. In this case, it is better to “do the best you can” and then continue as if nothing happened.