Mysql multiple insert query

Question

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.

This behavior is not reproducible on SQL Fiddle. The second query generates the same error as the first. — Gordon Linoff
– Gordon Linoff, Commented Mar 2, 2015 at 13:20
What happens if you create a new table called "users2" with exactly the same structure, and try running the queries again? It'd be interesting to see what data ends up in the table after you run each query. I can't see why both queries wouldn't fail. — thirtyish
– thirtyish, Commented Mar 2, 2015 at 13:23
@GordonLinoff, We got this in Server version: 5.5.37-0ubuntu0.12.04.1-log (Ubuntu) — Manu Jose K
– Manu Jose K, Commented Mar 2, 2015 at 13:28

fedorqui · Accepted Answer · 2015-03-02 13:33:07Z

5

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.)

edited Mar 2, 2015 at 13:33

fedorqui

293k112 gold badges592 silver badges640 bronze badges

answered Mar 2, 2015 at 13:29

Foreever

7,55410 gold badges57 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manu Jose K Over a year ago

In that case, what will be the default value for 'varchar' field?

Foreever Over a year ago

For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value. - dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

Collectives™ on Stack Overflow

Mysql multiple insert query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related