Part 1: Strict SQL Mode
Getting errors when you don't supply a value for a non-null column can be an immense source of frustration. There's a way to minimize the occurrence of such errors by setting a default value for those columns. Seems like an easy fix, but, as in all things, the devil's in the details. You have to be careful that you don't add a bunch of generic - and useless - data to your tables just for the sake of making INSERTs easier. In today's blog, we'll learn about the ramifications of MySQL's Strict SQL Mode, as well as how to view and set it using Navicat for MySQL 15. In part 2 we'll cover when it makes sense to employ default values (and when it doesn't).
Strict SQL Mode and Adjusted Values
In MySQL, you can control how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE by turning on Strict SQL Mode. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. If strict mode is not in effect, MySQL inserts adjusted values for both invalid or missing values and produces warnings. Examples of adjusted values would be an empty string, zero, and a timestamp/date of 0000-00-00 00:00:00.
It should be fairly obvious that adjusted values could undermine the whole point of having defaults. Hence, it's usually good idea to activate strict SQL mode and provide a default value where appropriate. In Navicat, you can check your current value for SQL Mode on the Variables tab of the Server Monitor. You'll find it under Tools > Server Monitor in the main menu.
Strict SQL mode is enabled if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is present. When deciding on which to use, note that the latter is more forgiving as, if a value is missing, MySQL inserts the appropriate adjusted value for the column data type and generates a warning rather than an error. Moreover, processing of the statement continues. Meanwhile, invalid values are converted to the closest valid value.
Strict SQL Mode in Action
Let's compare Strict SQL Mode to the default SQL mode using the Sakila Sample Database. The actor table does not allow nulls in any column, as evidenced by the checkboxes under the Not null header:
If we disable Strict SQL Mode for the current session using the SET command and perform an INSERT that only supplies the last_name, the database accepts it, but provides an empty string for the first_name:
If we re-activate Strict Mode, the same INSERT now fails with an error message:
Conclusion
In today's blog, we learned about the ramifications of MySQL's Strict SQL Mode, as well as how to view and set it using Navicat for MySQL 15. In part 2 we'll cover when and when it doesn't make sense to employ default values.
