5.8.4. Enabling Additional Input Data Restrictions

Strict mode turns on general input value restrictions, but it is not as strict as you can tell the MySQL server to be. When strict mode is in effect, certain SQL mode values enable additional restrictions on input data values:

• Division by zero can be treated as an error for data entry by enabling the ERROR_FOR_DIVISION_BY_ZERO mode value and strict mode. In this case, attempts to enter data via INSERT or UPDATE statements produce an error if an expression includes division by zero. (With ERROR_FOR_DIVISION_BY_ZERO but not strict mode, division by zero results in a value of NULL and a warning, not an error.)

  SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
  

• By default, MySQL allows “zero” dates ('0000-00-00') and dates that have zero parts ('2009-12-00', '2009-00-01'). Such dates are allowed even if you enable strict mode, but if you want to prohibit them, you can enable the NO_ZERO_DATE and NO_ZERO_IN_DATE mode values:

  SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';