Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Preventing MySQL from inserting implicit default values into not null columns

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.

like image 690
codingbiz Avatar asked May 24 '12 13:05

codingbiz


1 Answers

Issue:

SET SQL_MODE='STRICT_ALL_TABLES'

or put

SQL_MODE='STRICT_ALL_TABLES'

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

like image 162
Quassnoi Avatar answered Oct 23 '22 19:10

Quassnoi