1.9.6.2. Constraints on Invalid Data
Before MySQL 5.0.2, MySQL is forgiving of illegal or improper
data values and coerces them to legal values for data entry.
In MySQL 5.0.2 and up, that remains the default behavior, but
you can change the server SQL mode to select more traditional
treatment of bad values such that the server rejects them and
aborts the statement in which they occur.
Section 5.2.5, “The Server SQL Mode”.
This section describes the default (forgiving) behavior of
MySQL, as well as the newer strict SQL mode and how it
differs.
If you are not using strict mode, then whenever 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. column.
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 ae 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's
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 11.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 in non-strict mode 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's better to “do the best you
can” and then continue as if nothing happened.
In MySQL 5.0.2 and up, you can select stricter treatment of
input values by using the
STRICT_TRANS_TABLES or
STRICT_ALL_TABLES SQL modes:
SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';
STRICT_TRANS_TABLES enables strict mode for
transactional storage engines, and also to some extent for
non-transactional engines. It works like this:
For transactional storage engines, bad data values
occurring anywhere in a statement cause the statement to
abort and roll back.
For non-transactional storage engines, a statement aborts
if the error occurs in the first row to be inserted or
updated. (When the error occurs in the first row, the
statement can be aborted to leave the table unchanged,
just as for a transactional table.) Errors in rows after
the first do not abort the statement, because the table
has already been changed by the first row. Instead, bad
data values are adjusted and result in warnings rather
than errors. In other words, with
STRICT_TRANS_TABLES, a wrong value
causes MySQL to roll back all updates done so far, if that
can be done without changing the table. But once the table
has been changed, further errors result in adjustments and
warnings.
For even stricter checking, enable
STRICT_ALL_TABLES. This is the same as
STRICT_TRANS_TABLES except that for
non-transactional storage engines, errors abort the statement
even for bad data in rows following the first row. This means
that if an error occurs partway through a multiple-row insert
or update for a non-transactional table, a partial update
results. Earlier rows are inserted or updated, but those from
the point of the error on are not. To avoid this for
non-transactional tables, either use single-row statements or
else use STRICT_TRANS_TABLES if conversion
warnings rather than errors are acceptable. To avoid problems
in the first place, do not use MySQL to check column content.
It is safest (and often faster) to let the application ensure
that it passes only legal values to the database.
With either of the strict mode options, you can cause errors
to be treated as warnings by using INSERT
IGNORE or UPDATE IGNORE rather
than INSERT or UPDATE
without IGNORE.
