Null Columns in MySQL – Part II

Noel Herrick on January 23, 2014

I’ve covered some strange default behavior around nulls in MySQL. There’s another nuance to this issue: you can still insert rows without specifying values for not null columns. MySQL will helpfully give you a default value (this is not really helpful – true help would be a quick failure with a descriptive error message). Let’s walk through this example.

You can also set this in your config file (found at /etc/mysql/my.cnf on Linux systems):

sql-mode = "strict_all_tables"

Just note that this will change the behavior for all clients, so make sure you don’t have any code that depends on this “feature”.

Want to hear more quirks with MySQL? You can subscribe below and I’ll send you updates on a new book I’m writing that helps you navigate past the gotchas in MySQL. If you sign-up, I’ll also send out stuff on MySQL like tips and tutorials.