MySQL Gotchas - Insert statement - mysql automatically puts in datatype default values

Scenario:
1
When without sql_mode = TRADITIONAL or STRICT_something,
when you use INSERT statement without specifying explicitly all the data,
mysql 'helps' you to automatically put in default values depending on the column datatype:
if it's varchar, it puts in '' (empty string)
if its int, it puts in 0 (zero).

That's no good, and if you want the INSERT to fail with error if you didn't provide a value, you need to put
sql_mode = TRADITIONAL.

2
regardless of sql_mode = TRADITIONAL,
when you ALTER a table to add new columns, the existing rows don't have a value for those new columns,
MySQL automatically 'helps' you to put in default values for the datatype, '' or 0.





http://sql-info.de/mysql/gotchas.html
mysql insert statement inserts default value in not null field? - Google Search

MySQL Gotchas

sql-info.de/mysql/gotchas.html

As no value was provided in the INSERT statements, these can be ... If the column is declared as NOT NULL, the default value depends on the column type: . ..... value, or takes a guess at what might be an alternative value and silently inserts it ...

1. General SQL

1.1. NULL, or when NULL IS NOT NULL

In SQL NULL represents the the absence of a value. In MySQL an explicit NULL may also represent the next value of a pseudo-sequence and an implicit NULL may represent an implicit default value (a zero or empty string) determined by MySQL.

Example 1. When NULL is not NULL

CREATE TABLE null_1 (
  id INT NOT NULL,
  text1 VARCHAR(32) NOT NULL,
  text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);

INSERT INTO null_1 (id) VALUES(1);
INSERT INTO null_1 (text1) VALUES('test');

mysql> SELECT * FROM null_1;
+----+-------+-------+
| id | text1 | text2 |
+----+-------+-------+
|  1 |       | foo   |
|  0 | test  | foo   |
+----+-------+-------+
2 rows in set (0.00 sec)

Here MySQL has inserted an empty string into column text1 on the first row, and zero into column id on the second row, even though each column is defined as NOT NULL with no default value. As no value was provided in the INSERT statements, these can be considered an attempt to insert implicit NULLs into theses columns, which should normally cause the statements to fail.

	... If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type: ...
--http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html