Rechercher dans le manuel MySQL
1.8.3.3 Constraints on Invalid Data
By default, MySQL is forgiving of invalid or improper data values and coerces them to valid values for data entry. However, you can enable strict SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur. See Section 5.1.11, “Server SQL Modes”.
This section describes the default (forgiving) behavior of MySQL, as well as the 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.
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 does not start with a number into a numeric column, MySQL Server stores 0.
Invalid values for
ENUM
andSET
columns are handled as described in Section 1.8.3.4, “ENUM and SET Constraints”.MySQL permits you to store certain incorrect date values into
DATE
andDATETIME
columns (such as'2000-02-31'
or'2000-02-00'
). In this case, when an application has not enabled strict SQL mode, it up to the application to validate the dates before storing them. 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 takeNULL
values, an error occurs for single-rowINSERT
statements. For multiple-rowINSERT
statements or forINSERT INTO ... SELECT
statements, MySQL Server stores the implicit default value for the column data type. In general, this is0
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.7, “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 explicitDEFAULT
clause. If the definition has no suchDEFAULT
clause, MySQL inserts the implicit default value for the column data type.
The reason for using the preceding rules in nonstrict 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 is better to “do the best you can” and then continue as if nothing happened.
You can select stricter treatment of input values by using the
STRICT_TRANS_TABLES
or
STRICT_ALL_TABLES
SQL modes:
STRICT_TRANS_TABLES
enables
strict mode for transactional storage engines, and also to
some extent for nontransactional 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 nontransactional 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 nontransactional 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 nontransactional 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 nontransactional 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 valid 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
.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-constraint-invalid-data.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.