- 3.1 Data Value Categories
- 3.2 MySQL Data Types
- 3.3 How MySQL Handles Invalid Data Values
- 3.4 Working with Sequences
- 3.5 Expression Evaluation and Type Conversion
- 3.6 Choosing Data Types
This chapter is from the book
This chapter is from the book
This chapter is from the book
3.3 How MySQL Handles Invalid Data Values
Historically, the dominant principle for data handling in MySQL has been, “Garbage in, garbage out.” In other words, MySQL attempts to store any data value you give it, but if you don’t verify the value first before storing it, you may not like what you get back out. However, as of MySQL 5.0.2, several SQL modes are available 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.
By default, MySQL handles out-of-range or otherwise improper values 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.15).
These conversions are reported as warnings for statements such as INSERT, REPLACE, UPDATE, LOAD DATA, and ALTER TABLE. You can use SHOW WARNINGS after executing one of those statements to see the warning messages.
To turn on stricter checking of inserted or updated data values, 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 were described in Section 3.2.3, “Specifying Column Default Values.”
Strict mode actually does not enable the strictest checking that MySQL can perform. You can enable any or all of the following modes to impose additional constraints on input data:
- ERROR_FOR_DIVISION_BY_ZERO prevents entry of values if division by zero occurs in strict mode. (Without strict mode, a warning occurs and NULL is inserted.)
- NO_ZERO_DATE prevents entry of the “zero” date value in strict mode.
- NO_ZERO_IN_DATE prevents entry of incomplete date values that have a month or day part of zero in strict mode.
For example, to enable strict mode for all storage engines and also check for divide-by-zero errors, set the SQL mode like this:
mysql> SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_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_DATES 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 (which allows invalid values such as '2000-02-30' or '2000-06-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.