How MySQL Handles Invalid Data Values

In the past, the dominant principle for data handling in MySQL has been, "Garbage in, garbage out." In other words, if you don't verify data values first before storing them, you may not like what you get back out. However, as of MySQL 5.0.2, several SQL modes were introduced that enable you to reject bad values and cause an error to occur instead. The following discussion first discusses how MySQL handles improper data by default, and then covers the changes that occur when you enable the various SQL modes that affect data handling.

MySQL's default handling of out-of-range or otherwise improper values is as follows:

• For numeric or TIME columns, values that are outside the legal range are clipped to the nearest endpoint of the range and the resulting value is stored.

• For string columns other than ENUM or SET, strings that are too long are truncated to fit the maximum length of the column. Assignments to an ENUM or SET column depend on the values that are listed as legal in the column definition. If you assign to an ENUM column a value that is not listed as an enumeration member, the error member is assigned instead (that is, the empty string that corresponds to the zero-valued member). If you assign to a SET column a value containing substrings that are not listed as set members, those strings drop out and the column is assigned a value consisting of the remaining members.

• For date or time columns, illegal values are converted to the appropriate "zero" value for the type (see Table 3.14).

These conversions are reported as warnings for ALTER TABLE, LOAD DATA, UPDATE, INSERT INTO … SELECT, and multiple-row INSERT statements. In the mysql client, this information is displayed in the status line that is reported for a query. In a programming language, you may be able to get this information by some other means. If you're using the MySQL C or PHP APIs, you can invoke the mysql_info() function. With the Perl DBI API, you can use the mysql_info attribute of your database handle. The information provided is a count of the number of warnings.

To turn on stricter checking of data values for INSERT and UPDATE, enable one of the following SQL modes:

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
mysql> SET sql_mode = 'STRICT_TRANS_TABLES';

For transactional tables, both modes are identical. If an invalid or missing value is found, an error occurs, the statement aborts and rolls back, and has no effect. For non-transactional tables, the modes have the following effects:

• For both modes, if an invalid or missing value is found in the first row of a statement that inserts or updates rows, an error occurs. The statement aborts and has no effect, which is similar to what happens for transactional tables.

• If an error occurs after the first row in a statement that inserts or updates multiple rows, some rows already will have been modified. The two strict modes control whether the statement aborts at that point or continues to execute:

  • With STRICT_ALL_TABLES, an error occurs and the statement aborts. Rows affected earlier by the statement will already have been modified, so the result is a partial update.

  • With STRICT_TRANS_TABLES, MySQL aborts the statement for non-transactional tables only if doing so would have the same effect as for a transactional table. That is true only if the error occurs in the first row; an error in a later row leaves the earlier rows already changed. Those changes cannot be undone for a non-transactional table, so MySQL continues to execute the statement to avoid a partial update. It converts each invalid value to the closest legal value (as defined earlier in this section). For a missing value, MySQL sets the column to the implicit default for its data type. Implicit defaults are described in "Specifying Column Default Values."

Strict mode actually does not enable the strictest checking that MySQL can perform. You can use any or all of the following modes to impose additional constraints on input data:

• ERROR_FOR_DIVIDE_BY_ZERO causes errors for divide-by-zero operations.

• NO_ZERO_DATE prevents entry of the "zero" date value.

• NO_ZERO_IN_DATE prevents entry of incomplete date values that have a month or day part of zero.

For example, to enable strict mode for all table types and also check for divide-by-zero errors, set the SQL mode like this:

mysql> SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVIDE_BY_ZERO';

To turn on strict mode and all of the additional restrictions, you can simply enable trADITIONAL mode:

mysql> SET sql_mode = 'TRADITIONAL';

trADITIONAL is shorthand for "both strict modes, plus a bunch of other restrictions." This is more like the way that other "traditional" SQL DBMSs act with regard to data checking.

It is also possible to selectively weaken strict mode in some respects. If you enable the ALLOW_INVALID_DATE SQL mode, MySQL doesn't perform full checking of date parts. Instead, it requires only that months be in the range from 1 to 12 and days be in the range from 1 to 31. Another way to suppress errors is to use the IGNORE keyword with INSERT or UPDATE statements. With IGNORE, statements that would result in an error due to invalid values result only in a warning.

The various options available give you the flexibility to choose the level of validity checking that is appropriate for your applications.