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.
Search WWH ::




Custom Search