Null Columns in MySQL – Part II

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.

mysql> Create Table NullTable (a Int Not Null, b Int Not Null);
Query OK, 0 rows affected (0.01 sec)

Here’s a table with two not null columns.

mysql> Insert Into NullTable (a) Values (1);
Query OK, 1 row affected, 1 warning (0.01 sec)

We can totally insert, even though we didn’t specify a value for b and b is not null.

mysql> Show Warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1364 | Field 'b' doesn't have a default value |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

It does issue a warning, however.

mysql> Select * From NullTable;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
+---+---+
1 row in set (0.00 sec)

MySQL created a default value for us, which for ints is 0.

Once again, the solution to get the right behavior is:

Set sql_mode = "strict_all_tables";

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.



 No spam


Tagged with:
Posted in Databases, IT

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>