Database Reference
In-Depth Information
12.1. Using the SQL Mode to Reject Bad Input Values
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.
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 modes apply generally to all input values. Others apply to specific data types such
as dates.
Discussion
Normally, MySQL coerces input values 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 server loads the values into the table after coercing
them 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 | 1264 | Out of range value for column 'd' at row 1 |
+---------+------+--------------------------------------------+
mysql>
SELECT * FROM t;
+------+--------+------------+
| i | c | d |
+------+--------+------------+
| -1 | too-lo | 0000-00-00 |
+------+--------+------------+
One way to prevent these warnings is to check the input data on the client side to make
sure that it's legal. This is a reasonable strategy in certain circumstances (see the sidebar
in
Recipe 12.2
), but there is an alternative: let the server check data values on the server
side and reject them with an error if they're invalid.
To do this, set the
sql_mode
system variable to enable server restrictions on input data
acceptance. With the proper restrictions in place, data values that would otherwise result
