MySQL - 插入忽略

要透過 Node.js 程式將有效資料插入 MySQL 表中，我們使用 'IGNORE' 以及 'INSERT INTO' 語句，並使用 mysql2 庫的 query() 函式，如下所示：

sql = "INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)";
con.query(sql);

要透過 Java 程式將有效資料插入 MySQL 表中，我們使用 'IGNORE' 以及 'INSERT INTO' 語句，並使用 JDBC 函式 executeUpdate()，如下所示：

String sql = "INSERT IGNORE INTO table_name (column1, column2, column3, ...) 
VALUES (value1, value2, value3, ...)";
statement.executeUpdate(sql);

要透過 Python 程式將有效資料插入 MySQL 表中，我們使用 'IGNORE' 以及 'INSERT INTO' 語句，並使用 MySQL Connector/Python 的 execute() 函式，如下所示：

insert_ignore_query = "INSERT IGNORE INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...)"
cursorObj.execute(insert_ignore_query)

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating Table
  sql = "CREATE TABLE STUDENTS (ID int AUTO_INCREMENT,NAME varchar(5) NOT NULL UNIQUE,PRIMARY KEY (ID));"
  con.query(sql);

  sql = "INSERT INTO STUDENTS (NAME) VALUES ('Ajay'), ('Vinay'), ('Arjun');"
  con.query(sql);

  sql= "INSERT IGNORE INTO STUDENTS (NAME) VALUES ('Arjun');"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
      console.log("----------------------------------------");
  });

  sql = "SHOW WARNINGS;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("----------------------------------------");
  });

  sql = "SELECT * FROM STUDENTS;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});  

輸出

生成的輸出如下：

Connected!
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '',
  serverStatus: 2,
  warningStatus: 1,
  changedRows: 0
}
----------------------------------------
[
  {
    Level: 'Warning',
    Code: 1062,
    Message: "Duplicate entry 'Arjun' for key 'students.NAME'"
  }
]
----------------------------------------
[
  { ID: 1, NAME: 'Ajay' },
  { ID: 3, NAME: 'Arjun' },
  { ID: 2, NAME: 'Vinay' }
]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InsertIgnore {
  public static void main(String[] args) {
    String url = "jdbc:mysql://:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM STUDENTS";
            rs = st.executeQuery(sql);
            System.out.println("Table records before insert ignore: ");
            while(rs.next()) {
              String Id = rs.getString("Id");
              String Name = rs.getString("Name");
              System.out.println("Id: " + Id + ", Name: " + Name);
            }
            String sql1 = "INSERT INTO STUDENTS (NAME) VALUES (\"Arjun\")";
            st.executeUpdate(sql1);
            System.out.println("Record with name 'Arjun' inserted successfully...!");
            String sql2 = "SELECT * FROM STUDENTS";
            rs = st.executeQuery(sql2);
            System.out.println("Table record after insert: ");
            while(rs.next()) {
              String Id = rs.getString("Id");
              String Name = rs.getString("Name");
              System.out.println("Id: " + Id + ", Name: " + Name);
            }
            //now let use insert ignore query to insert a duplicate records into the Students table
            String sql3 = "INSERT IGNORE INTO STUDENTS (NAME) VALUES (\"Arjun\")";
            st.executeUpdate(sql3);
            System.out.println("Insert ignore query executed successfully....!");
            String sql4 = "SELECT * FROM STUDENTS";
            rs = st.executeQuery(sql4);
            System.out.println("Table records after insert ingore: ");
            while(rs.next()) {
              String Id = rs.getString("Id");
              String Name = rs.getString("Name");
              System.out.println("Id: " + Id + ", Name: " + Name);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                                 

輸出

獲得的輸出如下所示：

Table records before insert ignore: 
Id: 1, Name: Ajay
Id: 2, Name: Vinay
Record with name 'Arjun' inserted successfully...!
Table record after insert: 
Id: 1, Name: Ajay
Id: 9, Name: Arjun
Id: 2, Name: Vinay
Insert ignore query executed successfully....!
Table records after insert ingore: 
Id: 1, Name: Ajay
Id: 9, Name: Arjun
Id: 2, Name: Vinay       

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
insert_ignore_query = "INSERT IGNORE INTO tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (7, 'New Tutorial', 'John Doe', '2023-07-25')"
cursorObj.execute(insert_ignore_query)
connection.commit()
print("INSERT IGNORE query executed successfully.")
cursorObj.close()
connection.close()                                      

輸出

以下是上述程式碼的輸出：

INSERT IGNORE query executed successfully.