CODEWITHSUNDEEP
mysql
Atul
Atul

Reputation: 4340

INSERT IGNORE with invalid field value actually inserts Zero

I've in my table defined a field which is NOT NULL.

If I run a query INSERT IGNORE with null values it inserts a row with zero value instead of just ignoring the null value. (And the same happens even for values having invalid data type)

CREATE TABLE all_numbers (
    Numbers INT NOT NULL,
    AssociatedNumbers INT NOT NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO all_numbers (Numbers, AssociatedNumbers)
    VALUES (1,2), (3,4), (5,6), (7,8), (null, null);

And output is:

Numbers AssociatedNumbers
   1          2
   3          4
   5          6
   7          8
   0          0

Same is the case for:

INSERT IGNORE INTO all_numbers (Numbers, AssociatedNumbers)
    VALUES (1,2), (3,4), (5,6), (7,8), ('a', 'b');

Any idea why is this happening, and how to make MySQL to just ignore invalid values specified in the query? (Zeros instead of invalid value is really undesirable!)

UPDATE

Even after I enabled STRICT_ALL_TABLES mode its still not working. Here are screenshots:

Output of <code>SELECT @@GLOBAL.sql_mode;</code>

INSERT query output

Upvotes: 1

Views: 1694

Answers (2)

miken32
miken32

Reputation: 42709

I think you misunderstand what IGNORE is ignoring. It doesn't do value checking and ignore bad values, it just ignores or works around the errors that result from trying to insert those bad values. From the manual:

Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.

The "closest value" for a numeric column is zero:

If you try to store a string that does not start with a number into a numeric column, MySQL Server stores 0.

…

If you try to store NULL into a column that doesn't take NULL values… MySQL Server stores the implicit default value for the column data type. In general, this is 0 for numeric types…

As mentioned in another answer, setting the mode to strict will prevent this behaviour. You can set this in your session at run time with this query:

SET SESSION sql_mode = 'STRICT_ALL_TABLES';

To do this on a server-wide basis, start MySQL with --sql-mode="STRICT_ALL_TABLES" or add sql-mode="STRICT_ALL_TABLES" to your my.cnf configuration file.

Upvotes: 2

Ben Link
Ben Link

Reputation: 107

You should specify:

Set sql_mode = "strict_all_tables";

This will have the check to be more strict and give the correct results.

Upvotes: 1

Related Questions