CODEWITHSUNDEEP
phpmysqldatabasemysqli
Manu Jose K
Manu Jose K

Reputation: 238

Mysql multiple insert query

On mysql data insertion, I have encountered following issue. Here email field cannot be null. While executing following queries (Query 1 & 2), we are expecting errors. But query 1 gives error and query 2 gives success.

Table Structre

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `email` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

Query 1:

INSERT INTO `users` (`id`, `name`, `email`) VALUES (NULL, 'manu', NULL);

Result:-#1048 - Column 'email' cannot be null

Query 2:

INSERT INTO `users` (`id`, `name`, `email`)
VALUES (NULL, 'manu', NULL),(NULL, 'Jose', NULL);

Result:-2 rows inserted.

Both query returns success.

Is there any error in the query? Any thoughts will be appreciated.

Upvotes: 4

Views: 875

Answers (1)

Foreever
Foreever

Reputation: 7468

From documentation

Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

Upvotes: 5

Related Questions