What validation does MySQL do when strict mode is enabled?

Question

I was quite surprised when MySQL allowed me to insert a NULL into a field that was created with NOT NULL. I did some research and discovered how to enable strict mode. However, I am not quite sure what validation MySQL does when STRICT_ALL_TABLES is enabled.

The manual says:

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. (emphasis mine) For example, it might have the wrong data type for the column, or it might be out of range.

I understand what it considers missing and how it handles that. I am unclear what it considers invalid. I've done some testing and discovered the following:

• strings that are too long are invalid
• numbers that are out of range are invalid
• NULLs for a non-NULL column are invalid
• TRUE and FALSE always seem to be valid (they become 1 and 0 respectively)
• invalid dates are invalid
• zero dates are valid (additional modes can be enabled to change this behaviour)
• floats in an integer field are valid (they get rounded)
• letters in a number field are invalid

Does MySQL do any other validation checks other than what is mentioned above?

The manual says 'wrong data type for the column', but the only situation I see where this actually comes into play is letters in a number field. Are there other examples of data type errors?

Is there a list somewhere of exactly what checks MySQL performs?

EDIT: For the record, my application already does extensive validation. I am using strict mode as a last-chance, just-in-case check. If I forget to check something, I want it to fail fast rather than 'silently mangle my data'.

Answer 1

A good resource is to check the MySQL source, and read mysql-test/t/strict.test to see all the cases they test for after setting STRICT mode or TRADITIONAL mode (which is a superset of STRICT).

You did excellent research and testing, but there are a few more cases, such as:

• Trying to insert an undefined ENUM value.
• Trying to insert a default value for a NOT NULL column with no DEFAULT defined.
• Trying to use CAST() to convert strings to integers, etc.
• Conversion of VARCHAR to MEDIUMTEXT or LONGTEXT if you give a length greater than 65536.
• Truncation of COMMENT strings for tables and columns.
• Conversion of string to YEAR type.
• Defining a SET or ENUM column with duplicate entries.

Also mysql-test/include/strict_autoinc.inc, because it tests for overflow when an auto-inc value grows too large.

There are a few other test files that use STRICT mode for specific tests, but I didn't examine them.