NULL defaults to empty string in mysql?

Question

I have a strange situation with a field that doesn't allow NULL values. If I insert a row, the field defaults to the empty string, even when mysql claims that the default is NULL.

mysql> describe user;
+---------------------------+------------------+------+-----+---------+----------------+
| Field                     | Type             | Null | Key | Default | Extra          |
+---------------------------+------------------+------+-----+---------+----------------+
| id                        | int(30)          | NO   | PRI | NULL    | auto_increment |
| username                  | varchar(45)      | NO   | UNI | NULL    |                |
| city                      | varchar(45)      | NO   |     | NULL    |                |
+---------------------------+------------------+------+-----+---------+----------------+

mysql> show triggers;
Empty set (0.00 sec)

mysql> insert into user (username) values ('just_testing');
Query OK, 1 row affected, 17 warnings (0.01 sec)

This is the point where I go WHAT?! - city should default to NULL which isn't allowed, but look here:

mysql> select username, city from user where username = 'just_testing' and city is null;
Empty set (0.00 sec)

mysql> select username, city from user where username = 'just_testing' and city='';
+--------------+------+
| username     | city |
+--------------+------+
| just_testing |      |
+--------------+------+
1 row in set (0.00 sec)

Mysql has decided to use the empty string as default even though it isn't so and there isn't any triggers.

And further:

mysql> insert into user (username, city) values ('just_testing3', NULL);
ERROR 1048 (23000): Column 'city' cannot be null

What am I overlooking? How does the city column default to ''?

Answer 1

From the docs:

If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 11.6, “Data Type Default Values”. See also Section 1.8.3.3, “Constraints on Invalid Data”.

and further:

For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.