Preventing MySQL from inserting implicit default values into not null columns

Question

I am trying to use multiple inserts (in one statement) and I have this table structure ``` CREATE TABLE Scores ( studentID varchar(50) not null, score int ) ENGINE = InnoDB ``` My Query: ``` INSERT INTO Scores Values ('Barry', 45), (NULL, 41), ('Jones', 53) ``` This statement (I expected) should fail since [StudentID] column does not accept NULL. The problem was MySQL inserted empty string ('') into row 2... and allow the rest to continue.

Accepted Answer

Issue:

```
SET SQL_MODE='STRICT_ALL_TABLES'
```

or put

```
SQL_MODE='STRICT_ALL_TABLES'
```

under `[mysqld]` into `my.cnf` (then restart `MySQL`).

Preventing MySQL from inserting implicit default values into not null columns

Solution

Problem