MySQL Cookbook, 2nd Edition

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 ...