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 ''?

You are inserting just_testing2 and reading back just_testing, is it a typo? — Quassnoi
– Quassnoi, Commented Jul 1, 2015 at 17:28
... not null default null ... is a contradiction and a flaw in the creation of the table from my point of view. The expected behavior is one out of two possible ones and mysql decided to choose the other one ... — Axel Amthor
– Axel Amthor, Commented Jul 1, 2015 at 17:37
@AxelAmthor: you can't have not null default null in MySQL, it's just the way DESCRIBE shows things. — Quassnoi
– Quassnoi, Commented Jul 1, 2015 at 17:43

Quassnoi · Accepted Answer · 2015-07-01 17:33:34Z

7

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.

answered Jul 1, 2015 at 17:33

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mzedeler Over a year ago

So the reason this is possible is because mysql is running in non-strict mode?

mzedeler Over a year ago

Thanks a bunch. I found this very informative page with a lot of other gotchas as well: sql-info.de/mysql/gotchas.html

Antoine Dahan · Accepted Answer · 2015-07-01 17:37:32Z

1

You are making a column that cannot be null have a default value of NULL. Alter the column to allow null as a value like this:

ALTER TABLE `user`
ALTER COLUMN `city` varchar(45) DEFAULT NULL

answered Jul 1, 2015 at 17:37

Antoine Dahan

7332 gold badges13 silver badges27 bronze badges

1 Comment

mzedeler Over a year ago

It isn't my database schema and I just want to find what made the insert in my example work (I have running code that uses this). It is definitely not how I'd do it.

Pallav Agarwal · Accepted Answer · 2015-07-01 17:48:49Z

1

In the output for describe user;, the NULL column shows NO for all three fields. That means that a NULL value is not allowed in that field.

You can check how the table was created (in case you didn't create it) with SHOW CREATE TABLE user;.

This example should be helpful:

mysql> create table test(firstname VARCHAR(45) NOT NULL, lastname VARCHAR(45));


mysql> describe test;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| firstname | varchar(45) | NO   |     | NULL    |       |
| lastname  | varchar(45) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

In this case lastname can have a value of NULL while firstname can't, even though default for both is shown as NULL.

To change the format of the table to accept NULL values, you should probably do :

ALTER TABLE `user`
ALTER COLUMN `city` VARCHAR(45) NULL

answered Jul 1, 2015 at 17:48

Pallav Agarwal

11810 bronze badges

3 Comments

mzedeler Over a year ago

As you can see from the correct answer above, it is actually possible to have a colum marked as NOT NULL that still accepts empty values (in the sense that you INSERT into the table and don't specify them). I find this behaviour pretty ridiculous, but mysql supports it nonetheless.

Pallav Agarwal Over a year ago

@mzedeler I only said it can't have a NULL value if the column is marked as NOT NULL. As it happened in your case, mysql gave it an empty value instead. Empty value is not equivalent to NULL value as one can see by trying to get length of fields. Length of empty would be 0, while that of NULL would be NULL.

mzedeler Over a year ago

I know the difference between the empty string and NULL. My question was why mysql clearly was accepting what most other databases treats as invalid inserts, because non-specified fields defaults to NULL or what the column is specified to default to.

user2473015 · Accepted Answer · 2015-07-01 17:48:01Z

0

When you creating table you have specified that City can not be NULL

answered Jul 1, 2015 at 17:48

user2473015

1,4523 gold badges24 silver badges51 bronze badges

Collectives™ on Stack Overflow

NULL defaults to empty string in mysql?

4 Answers 4

2 Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related