Recipe 10.20. Using the SQL Mode to Control Bad Input Data Handling

Problem

By default, MySQL is forgiving about accepting data values that are invalid, out of range, or otherwise unsuitable for the data types of the columns into which you insert them. (The server accepts the values and attempts to coerce them to the closest legal value.) But you want the server to be more restrictive and not accept bad data.

Solution

Set the SQL mode. Several mode values are available to control how strict the server is. Some of these modes apply generally to all input values. Others apply to specific data types such as dates.

Discussion

Normally, MySQL accepts data and coerces it to the data types of your table columns if the input doesn't match. Consider the following table, which has integer, string, and date columns:

mysql> CREATE TABLE t (i INT, c CHAR(6), d DATE);             

Inserting a row with unsuitable data values into the table causes warnings (which you can see with SHOW WARNINGS), but the values are loaded into the table after being coerced to whatever the closest legal value is (or at least to some value that fits the column):

mysql> INSERT INTO t (i,c,d) VALUES('-1x','too-long string!','1999-02-31'); mysql> SHOW WARNINGS; +---------+------+----------------------------------------+ | Level   | Code | Message                                | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'i' at row 1 | | Warning | 1265 | Data truncated for column 'c' at row 1 | | Warning | 1265 | Data truncated for column 'd' at row 1 | +---------+------+----------------------------------------+ mysql> SELECT * FROM t; +------+--------+------------+ | i    | c      | d          | +------+--------+------------+ |   -1 | too-lo | 0000-00-00 | +------+--------+------------+ 

Prior to MySQL 5.0, the way to prevent these warnings is to check the input data on the client side to make sure that it's legal. This remains a reasonable strategy in certain circumstances (see the sidebar "Server-Side Versus Client-Side Validation" in Section 10.21), but MySQL 5.0 and up offers you an alternative: let the server check data values on the server side and reject them with an error if they're invalid. Then you don't have to check them.

To use this strategy, enable restrictions on input data acceptance by setting the sql_mode system variable. You can set the SQL mode to cause the server to be much more restrictive than it is by default. With the proper restrictions in place, data values that would otherwise result in conversions and warnings result in errors instead. Try the preceding INSERT again after enabling "strict" SQL mode:

mysql> SET sql_mode = 'STRICT_ALL_TABLES'; mysql> INSERT INTO t (i,c,d) VALUES('-1x','too-long string!','1999-02-31'); ERROR 1265 (01000): Data truncated for column 'i' at row 1 

Here the statement doesn't even progress to the second and third data values because the first is invalid for an integer column and the server raises an error.

Even without enabling input restrictions, the server as of MySQL 5.0 is a bit more strict about date checking than previous versions. Before 5.0, the server checks only that the month and day parts of date values are in the ranges 1 to 12 and 1 to 31, respectively. This allows a date such as '2005-02-31' to be entered. As of MySQL 5.0, the month must be from 1 to 12 (as before), but the day value must be legal for the given month. This means that '2005-02-31' generates a warning now by default.

Although date checking is somewhat more restrictive as of MySQL 5.0, MySQL still allows dates such as '1999-11-00' or '1999-00-00' that have zero parts, or the "zero" date ('0000-00-00'), and this is true even in strict mode. If you want to restrict these kinds of date values, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes to cause warnings, or errors in strict mode. For example, to disallow dates with zero parts or "zero" dates, set the SQL mode like this:

mysql> SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';             

A simpler way to enable these restrictions, and a few more besides, is to enable TRADITIONAL SQL mode. trADITIONAL mode is actually a constellation of modes, as you can see by setting the sql_mode value and then displaying it:

mysql> SET sql_mode = 'TRADITIONAL'; mysql> SELECT @@sql_mode\G *************************** 1. row *************************** @@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,             NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,             NO_AUTO_CREATE_USER 

You can read more about the various SQL modes in the MySQL Reference Manual.

The examples shown set the session value of the sql_mode system variable, so they change the SQL mode only for your current connection. To set the mode globally for all clients, set the global sql_mode value (this requires the SUPER privilege):

mysql> SET GLOBAL sql_mode = '                 mode_value                 ';