Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Insert Ignore

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Insert Ignore

      In MySQL, the INSERT INTO statement can be used to insert one or more records into a table.
      In some scenarios, if a particular column has a UNIQUE constraint and if we are trying to add duplicates records into that particular column using the INSERT INTO statement, MySQL will terminate the statement and returns an error. As the result, no rows are inserted into the table.

      MySQL Insert Ignore Statement

      However, if we use the MySQL INSERT IGNORE INTO statement, it will not display an error. Instead, it allows us to insert valid data into a table and ignores the rows with invalid data that would cause errors.
      Following are some scenarios where the INSERT IGNORE INTO statement avoid errors:
      • When we insert a duplicate value in the column of a table that has UNIQUE key or PRIMARY key constraints.
      • When we try to add NULL values to a column where it has NOT NULL constraint on it.

      Syntax

      Following is the syntax of the INSERT IGNORE statement in MySQL 
      INSERT IGNORE INTO table_name (column1, column2, column3, ...)
      VALUES (value1, value2, value3, ...);

      Example

      First of all, let us create a table named CUSTOMERS using the following query below
      Note: The UNIQUE constraint ensures that no duplicate value can be stored or inserted in the NAME column.
      CREATE TABLE CUSTOMERS (
         ID int NOT NULL,
         NAME varchar(20) NOT NULL UNIQUE,
         PRIMARY KEY (ID)
      );
      The following query inserts three records into the CUSTOMERS table 
      INSERT INTO CUSTOMERS (ID, NAME) 
      VALUES (1, "Ajay"), (2, "Vinay"), (3, "Arjun");
      Execute the following query to display the records present in the CUSTOMERS table 
      SELECT * FROM CUSTOMERS;
      Following are the records of CUSTOMERS table
      ID
      NAME
      1
      Ajay
      2
      Arjun
      3
      Vinay
      Now, let us insert a duplicate record into the NAME column of CUSTOMERS table using the below query 
      INSERT INTO CUSTOMERS (NAME) VALUES (2, "Arjun");
      It returns an error because the NAME "Arjun" is already present in the column and hence it violates the UNIQUE constraint.
      ERROR 1062 (23000): Duplicate entry 'Arjun' for key 'customers.NAME'
      Now, let us use the INSERT IGNORE statement as shown below 
      INSERT IGNORE INTO CUSTOMERS (NAME) VALUES (2, "Arjun");

      Output

      Though we are inserting a duplicate value, it do not display any error, instead it gives a warning.
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      We can find the details of the above warning using the following query 
      SHOW WARNINGS;
      Following is the warnings table
      Level
      Code
      Message
      Warning
      1062
      Duplicate entry 'Arjun' for key 'customers.NAME'

      Verification

      If we try to verify the CUSTOMERS table, we can find that the duplicate row which we tried to insert will not be present in the table.
      SELECT * FROM CUSTOMERS;
      The output for the program above is produced as given below
      ID
      NAME
      1
      Ajay
      2
      Arjun
      3
      Vinay

      MySQL INSERT IGNORE and STRICT mode

      The strict mode controls how MySQL handles the invalid, missing, or out of range values that are going to be added into a table through data-change statements such as INSERT or UPDATE.
      So, if the strict mode is ON, and we are trying to insert some invalid values into a table using the INSERT statement, MySQL terminates the statement returns an error message.
      However, if we use the INSERT IGNORE INTO statement, instead of returning an error, MySQL will adjust those values to make them valid before adding the value to the table.

      Example

      Let us create a table named CUSTOMERS using the following query
      Note: The NAME column accepts only strings whose length is less than or equal to five.
      CREATE TABLE CUSTOMERS (
         ID int NOT NULL,
         NAME varchar(5),
         PRIMARY KEY (ID)
      );
      Here, we are trying to insert a value into NAME column whose length is greater than 5.
      INSERT INTO CUSTOMERS (NAME) VALUES (1, "Malinga");
      It returns an error as shown below 
      ERROR 1406 (22001): Data too long for column 'NAME' at row 1
      Now, we are trying to use the INSERT IGNORE statement to insert the same string
      INSERT IGNORE INTO CUSTOMERS (NAME) VALUES (1, "Malinga");

      Output

      As we can see in the output, instead of returning an error, it displays an warning 
      Query OK, 1 row affected, 1 warning (0.01 sec)
      Let us find the details of the above warning using the following command 
      SHOW WARNINGS;
      As we can see in the output below, MySQL truncated the data before inserting it into the CUSTOMERS table.
      Level
      Code
      Message
      Warning
      1265
      Data truncated for column 'NAME' at row 1

      Verification

      Execute the following query to verify the records of the CUSTOMERS table 
      Select * from CUSTOMERS;
      As we can see in the CUSTOMERS table below, the value has been truncated to 5 characters and inserted into the table.
      ID
      NAME
      1
      Malin

      Insert Ignore Query Using a Client Program

      Besides using MySQL queries to perform the Insert Ignore operation, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

      Syntax

      Following are the syntaxes of this operation in various programming languages
      PHPNodeJSJavaPython
      To to insert valid data into a MySQL table through a PHP program, we use the 'IGNORE' along with 'INSERT INTO' statement using the mysqli function query() as follows 
      $sql = "INSERT IGNORE INTO table_name (column1, column2, column3, ...) 
      VALUES (value1, value2, value3, ...)"; 
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      if($mysqli->connect_errno ) {
         printf("Connect failed: %s", $mysqli->connect_error);
         exit();
      }
      //printf('Connected successfully.');
      $sql = "INSERT IGNORE INTO tutorials_tbl values(5, 'Java Tutorial', 'newauther3', '2022-11-15')";
      if($result = $mysqli->query($sql)){
         printf("Data inserted successfully..!");
      }
      $q = "SELECT * FROM tutorials_tbl where tutorial_id = 5";
      if($res = $mysqli->query($q)){
      printf("Records after insert ignore statement: ");
      while($row = mysqli_fetch_row($res)){
         print_r ($row);
      }
      }
      if($mysqli->error){
         printf("Failed..!" , $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Data inserted successfully..!Records after insert ignore statement: Array
      (
        [0] => 5
        [1] => Java Tutorial
        [2] => newauther3
        [3] => 2022-11-15
      )