how to INSERT into a table if no WARNING_COUNT, Mysql,php

Question

I am trying to insert some blind values that is exported from csv to database. During the process it is inserting anything I give without even showing me any warnings.

example: I have a table structure as below

  #   column-name     type
  --   ---            ----
  1   id             int(11)
  2   name           varchar(32)
  3   bool           tinyint(1)

insert query:

INSERT INTO `table_name` (`id`, `name`, `bool`) VALUES ('a', '11111', 'abced');

When I try to run the above query it does inserts a row but pops out a warning.. I found a solution that I understand it is not the right way.

my try

INSERT INTO `table_name` (`id`, `name`, `bool`) VALUES ('a', '11111', 'abced');

php

$warningCount = count fetched after executing the query "SELECT @@warning_count"
if($WarningCount > 0){ DELETE THE ROW INSERTED and ECHO error Message}

Answer 1

Try setting SQL mode to STRICT_ALL_TABLES.

mysql> set @@session.sql_mode = STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t14 (id integer, name varchar(32), bool tinyint(1));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t14 set id=1, name="test", bool="bla";
ERROR 1366 (HY000): Incorrect integer value: 'bla' for column 'bool' at row 1

MySQL documentation states:

Enable strict mode for all storage engines. Invalid data values are rejected. Additional detail follows.

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range.

Then insert in transaction. Failed insert queries will no be counted. Keep in mind that MySQL transactions are not fully atomic in the sense of the “all or nothing” rule. You can commit a transaction with half of the statements done, and the other half failed.