5.8. Handling Missing or Invalid Data Values

Many database servers perform a great deal of value checking for data inserted into tables and generate errors for invalid input values that don't match column data types. MySQL, on the other hand, historically has been non-traditional and more "forgiving"in its data handling: The MySQL server converts erroneous input values to the closest legal values (as determined from column definitions) and continues on its way. For example, if you attempt to store a negative value into an UNSIGNED column, MySQL converts it to zero, which is the nearest legal value for the column. This forgiving behavior stems from MySQL's origins, which did not include transactional storage engines. Because a failed or erroneous transaction could not be rolled back, it was deemed preferable to convert the input values as well as possible and continue on, rather than perform a partial insert or update operation that processes only some of the rows specified in a data-modifying statement.

MySQL now includes transactional storage engines and in MySQL 5 you can tell the server to check input values more restrictively and to reject invalid values. The following discussion describes how to control whether rejection of invalid input values should occur, and the circumstances under which conversions take place if you allow them. The discussion is framed in terms of INSERT statements, but REPLACE and UPDATE are handled similarly.

The choice of how strict to be is up to individual applications. If the default forgiving behavior is suitable, you can continue to use that behavior. An application that requires more restrictive checking and needs to see errors for invalid input data can select that behavior instead. The behavior is configurable for each client by setting the SQL mode through use of the sql_mode system variable. In this way, MySQL Server accommodates a broad range of application requirements. General information about setting the SQL mode is given in Section 1.3, "Server SQL Modes." The following discussion focuses on using the SQL mode to control input data handling.

By default, the server uses a sql_mode value of '' (the empty string), which enables no restrictions. Thus, the server operates in forgiving mode by default. To set the mode this way explicitly, use the following statement:

 SET sql_mode = ''; 

The most general means of enabling input value restrictions is by using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES modes:

 SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 'STRICT_ALL_TABLES'; 

The term "strict mode" refers collectively to both of these modes. They prevent entry of invalid values such as those that are out of range, or NULL specified for NOT NULL columns.

Another SQL mode, trADITIONAL, enables strict mode plus other restrictions on date checking and division by zero. Setting the sql_mode system variable to TRADITIONAL causes MySQL to act like more traditional database servers in its input data handling:

 SET sql_mode = 'TRADITIONAL'; 

The differences between the two strict modes are discussed later, as are the additional restrictions turned on by TRADITIONAL mode.

In many cases, type conversion affords you the flexibility to write a statement different ways and get the same result. For example, if i is an integer column, the following statements both insert 43 into it, even though the value is specified as a number in one statement and as a string in the other. MySQL performs automatic string-to-number conversion for the second statement:

 INSERT INTO t (i) VALUES(43); INSERT INTO t (i) VALUES('43'); 

MySQL also performs a conversion to 43 for the following statement, but it generates a warning as well because the conversion changes the value:

 INSERT INTO t (i) VALUES('43x'); 

In this case, the string '43x' is not completely numeric, so you may want it to be rejected as invalid with an error rather than a warning. You can do this by enabling strict SQL mode.

When MySQL performs type conversions that change values, it generates warnings that can be displayed with the SHOW WARNINGS statement.

5.8.1. Handling Missing Values

In MySQL, INSERT statements may be incomplete in the sense of not specifying a value for every column in a table. Consider the following table definition:

 CREATE TABLE t (     i INT NULL,     j INT NOT NULL,     k INT DEFAULT -1 ); 

For this table, an INSERT statement is incomplete unless it specifies values for all three columns in the table. Each of the following statements is an example of a statement that is missing column values:

 INSERT INTO t (i) VALUES(0); INSERT INTO t (i,k) VALUES(1,2); INSERT INTO t (i,k) VALUES(1,2),(3,4); INSERT INTO t VALUES(); 

In the last statement, the empty VALUES list means "use the default value for all columns."

MySQL handles missing values as follows:

• If the column definition contains a DEFAULT clause, MySQL inserts the value specified by that clause. Note that MySQL adds a DEFAULT NULL clause to the definition if it has no explicit DEFAULT clause and the column can take NULL values. Thus, the definition of column i actually has DEFAULT NULL in its definition:

   mysql> SHOW CREATE TABLE t\G *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `i` int(11) default NULL,   `j` int(11) NOT NULL,   `k` int(11) default '-1' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

  
  

• If a column definition has no DEFAULT clause, missing-value handling depends on whether strict SQL mode is in effect and whether the table is transactional:

  • If strict mode is not in effect, MySQL inserts the implicit default value for the column data type and generates a warning.

  • If strict mode is in effect, an error occurs for transactional tables (and the statement rolls back). An error occurs for non-transactional tables as well, but a partial update might result: If the error occurs for the second or later row of a multiple-row insert, the earlier rows will already have been inserted.

  The definition of column j has no DEFAULT clause, so INSERT statements that provide no value for j are handled according to these rules.

DEFAULT clause specification and implicit default values are discussed in Section 5.6, "Column Attributes."

5.8.2. Handling Invalid Values in Non-Strict Mode

In general, when operating in non-strict mode, MySQL performs type conversion based on the constraints implied by a column's definition. These constraints apply in several contexts:

• When you insert or update column values with statements such as INSERT, REPLACE, UPDATE, or LOAD DATA INFILE.

• When you change a column definition with ALTER TABLE.

• When you specify a default value using a DEFAULT value clause in a column definition. (For example, if you specify a default value of '43' for a numeric column, that string is converted to 43 when the default value is used.)

If MySQL is not operating in strict mode, it adjusts invalid input values to legal values when possible and generates warning messages. These messages can be displayed with the SHOW WARNINGS statement.

The following list discusses some of the conversions that MySQL performs. It isn't exhaustive, but is sufficiently representative to provide you with a good idea of how MySQL treats input values and what you'll be tested on for the exam.

• Conversion of out-of-range values to in-range values. If you attempt to store a value that's smaller than the minimum value allowed by the range of a column's data type, MySQL stores the minimum value in the range. If you attempt to store a value that's larger than the maximum value in the range, MySQL stores the range's maximum value. For example, TINYINT has a range of 128 to 127. If you attempt to store values less than 128 in a TINYINT column, MySQL stores 128 instead. Similarly, MySQL stores values greater than 127 as 127. If you insert a negative value into an UNSIGNED numeric column, MySQL converts the value to 0.

• String truncation. String values that are too long are truncated to fit in the column. If you attempt to store 'Sakila' into a CHAR(4) column, MySQL stores it as 'Saki' and discards the remaining characters. (It is not considered an error to trim trailing spaces, so MySQL will insert 'Saki ' into the column as 'Saki' without generating a warning.)

• Enumeration and set value conversion. If a value that's assigned to an ENUM column isn't listed in the ENUM definition, MySQL converts it to '' (the empty string). If a value that's assigned to a SET column contains elements that aren't listed in the SET definition, MySQL discards those elements, retaining only the legal elements.

• Conversion to data type default. If you attempt to store a value that cannot be converted to the column data type, MySQL stores the implicit default value for the type. For example, if you try to store the value 'Sakila' in an INT column, MySQL stores the value 0. The "zero" value is '0000-00-00' for date columns and '00:00:00' for time columns. The implicit default value for each data type is given in Section 5.6, "Column Attributes."

• Handling assignment of NULL to NOT NULL columns. The effect of assigning NULL to a NOT NULL column depends on whether the assignment occurs in a single-row or multiple-row INSERT statement. For a single-row INSERT, an error occurs and the statement fails. For a multiple-row INSERT, MySQL assigns the column the implicit default value for its data type.

Using ALTER TABLE to change a column's data type maps existing values to new values according to the constraints imposed by the new data type. This might result in some values being changed. For example, if you change a TINYINT to an INT, no values are changed because all TINYINT values fit within the INT range. However, if you change an INT to a TINYINT, any values that lie outside the range of TINYINT are clipped to the nearest endpoint of the TINYINT range. Similar effects occur for other types of conversions, such as TINYINT to TINYINT UNSIGNED (negative values are converted to zero), and converting a long string column to a shorter one (values that are too long are truncated to fit the new size).

If a column is changed to NOT NULL using ALTER TABLE, MySQL converts NULL values to the implicit default value for the data type.

The following table shows how several types of string values are handled when converted to DATE or INT data types. It demonstrates several of the points just discussed. Note that only string values that look like dates or numbers convert properly without loss of information.

String Value	Converted to DATE	Converted to INT
'2010-03-12'	'2010-03-12'	2010
'03-12-2010'	'0000-00-00'	3
'0017'	'0000-00-00'	17
'500 hats'	'0000-00-00'	500
'bartholomew'	'0000-00-00'	0

5.8.3. Handling Invalid Values in Strict Mode

Input values may be invalid for a number of reasons:

• For a numeric or temporal column, a value might be out of range.

• For a string column, a string might be too long.

• For an ENUM column, a value might be specified that is not a legal enumeration value or as part of a value For a SET column, a value might contain an element that is not a set member.

• For a column that is defined as NOT NULL, a value of NULL might have been given.

Enabling strict mode turns on general input value restrictions. In strict mode, the server rejects values that are out of range, have an incorrect data type, or are missing for columns that have no default. Strict mode is enabled using the STRICT_TRANS_TABLES and STRICT_ALL_TABLES mode values.

STRICT_TRANS_TABLES enables strict behavior for errors that can be rolled back or canceled without changing the table into which data is being entered. If an error occurs for a transactional table, the statement aborts and rolls back. For a non-transactional table, the statement can be aborted without changing the table if an invalid value occurs in a single-row insert or the first row of a multiple-row insert. Otherwise, to avoid a partial update for a non-transactional table, MySQL adjusts any invalid value to a legal value, inserts it, and generates a warning. (Adjustment of NULL inserted into a NOT NULL column is done by inserting the implicit default value for the column data type.)

STRICT_ALL_TABLES is similar to STRICT_TRANS_TABLES but causes statements for non-transactional tables to abort even for errors in the second or later rows of a multiple-row insert. This means that a partial update might occur, because rows earlier in the statement will already have been inserted.

5.8.4. Enabling Additional Input Data Restrictions

Strict mode turns on general input value restrictions, but it is not as strict as you can tell the MySQL server to be. When strict mode is in effect, certain SQL mode values enable additional restrictions on input data values:

• Division by zero can be treated as an error for data entry by enabling the ERROR_FOR_DIVISION_BY_ZERO mode value and strict mode. In this case, attempts to enter data via INSERT or UPDATE statements produce an error if an expression includes division by zero. (With ERROR_FOR_DIVISION_BY_ZERO but not strict mode, division by zero results in a value of NULL and a warning, not an error.)

   SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; 

  
  

• By default, MySQL allows "zero" dates ('0000-00-00') and dates that have zero parts ('2009-12-00', '2009-00-01'). Such dates are allowed even if you enable strict mode, but if you want to prohibit them, you can enable the NO_ZERO_DATE and NO_ZERO_IN_DATE mode values:

   SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE'; 

  
  

The TRADITIONAL mode value is a composite mode that enables strict mode as well as the other restrictions just described. If you want your MySQL server to be as restrictive as possible about input data checking (and thus to act like other "traditional" database servers), the simplest way to achieve this is to enable trADITIONAL mode rather than a list of individual more-specific modes:

 SET sql_mode = 'TRADITIONAL'; 

Setting the SQL mode by using TRADITIONAL has the additional advantage that if future versions of MySQL implement other input data restrictions that become part of trADITIONAL mode, you won't have to explicitly enable those modes to take advantage of them.

5.8.5. Overriding Input Data Restrictions

To override input data restrictions that may be enabled, use INSERT IGNORE or UPDATE IGNORE rather than just INSERT or UPDATE (without IGNORE). The IGNORE keyword causes MySQL to use non-strict behavior for the statement (for example, to produce warnings rather than errors).

Before MySQL 5, date values were required only to have month and day values in the range from 1 to 12 and 1 to 31, respectively. This means that MySQL accepted dates such as '2009-02-31'. MySQL 5 requires that month and day values correspond to an actual legal date, so '2009-02-31' is not considered a valid date. MySQL converts it to '0000-00-00' and generates a warning. In strict mode, '2009-02-31' results in an error.

If you want relaxed date checking that requires only that month and day values be in the respective ranges of 1 to 12 and 1 to 31, enable the ALLOW_INVALID_DATES SQL mode value:

 SET sql_mode = 'ALLOW_INVALID_DATES'; 

You can use ALLOW_INVALID_DATES for relaxed date checking even in strict mode:

 SET sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';