Database Reference
In-Depth Information
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.
Without input restrictions enabled, the server checks that the month part of date values
is in the range from 1 to 12 and that the day value is legal for the given month. This
means that
'2005-02-31'
generates a warning by default (with conversion to
'0000-00-00'
). In strict mode, an error occurs.
MySQL still permits dates such as
'1999-11-00'
or
'1999-00-00'
that have zero parts,
or the “zero” date (
'0000-00-00'
), and (until MySQL 5.7.4) this is true even in strict
mode. 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
prohibit 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
TRADI
TIONAL
SQL mode.
TRADITIONAL
mode is actually a constellation of modes, as you can
see by setting and displaying the
sql_mode
value:
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 session. To set the mode globally for all
clients, start the server with a
--sql_mode=
mode_value
option. Alternatively, if you have
the
SUPER
privilege, you can set the global mode at runtime:
mysql>
SET GLOBAL sql_mode = '
mode_value
';
12.2. Validating and Transforming Data
Problem
You must make sure that the data values in a file are legal.