What validation does MySQL do when strict mode is enabled?
mysql, strict, validation
Solution
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.
Problem
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 - `NULL`s 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'.