CODEWITHSUNDEEP
mysqlsql
mzedeler
mzedeler

Reputation: 4381

NULL defaults to empty string in mysql?

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

Upvotes: 8

Views: 8504

Answers (4)

Pallav Agarwal
Pallav Agarwal

Reputation: 118

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

Upvotes: 1

user2473015
user2473015

Reputation: 1442

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

Upvotes: 0

Antoine Dahan
Antoine Dahan

Reputation: 713

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425823

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.

Upvotes: 7

Related Questions