Why is MySQL changing null to zero?

I'm completely new to databases and SQL.

I have a column marked NOT NULL.

When I forget to give a value to that column when doing an INSERT, it inserts a 0 value in that column.

Is there anything I can do t开发者_如何转开发o make it return an error instead of changing the NULL to 0?

You want to have a look at the SQL_MODE configuration. It allows you to define how strict MySQL handles such things. By default it is pretty lenient which is not what I usually want. It also depends on the data type, especially with Dates it is less than optimal by default.

I personally go for STRICT_ALL_TABLES usually.

See the MySQL manual (5.0) here:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

There are two possible reasons for this:

• You have a default value defined for that column
• Your column is INT NOT NULL and casts NULL to INT, which results in 0

The second situation seems to be the case here, obviously. If you want to get an error instead, you could change the SQL_MODE to be strict. An other possibility is to do the input validation in your progam rather than leaving it to SQL.

I guess you have a DEFAULT value for that column