MySQL − 在重複鍵上更新插入

要在 MySQL 表中透過 Node.js 程式更新重複行，我們使用 DUPLICATE KEY UPDATE 以及使用mysql2庫的query()函式的INSERT語句，如下所示：

sql = "INSERT INTO my_table (column1, column2, ...)
VALUES  (value1, value2), (value3, value4), ...
ON DUPLICATE KEY UPDATE
 column1 = value1,
 column2 = value2, ...";  
con.query(sql);

要在 MySQL 表中透過 Java 程式更新重複行，我們使用 DUPLICATE KEY UPDATE 以及使用JDBC函式executeUpdate()的INSERT語句，如下所示：

String sql = "INSERT INTO my_table (column1, column2, ...) 
VALUES (value1, value2), (value3, value4), ... 
ON DUPLICATE KEY UPDATE
 column1 = value1,
 column2 = value2, ...";
statement.executeUpdate(sql);

要在 MySQL 表中透過 Python 程式更新重複行，我們使用 DUPLICATE KEY UPDATE 以及使用 MySQL Connector/Python 的execute()函式的INSERT語句，如下所示：

  
insert_on_duplicate_key_update_query = "INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...) 
ON DUPLICATE KEY UPDATE 
column1 = VALUES(column1), column2 = VALUES(column2), ..."
cursorObj.execute(insert_on_duplicate_key_update_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!");
  console.log("----------------------------------------");

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

  //Creating Table
  sql = "CREATE TABLE Actors (ID int auto_increment,NAME varchar(20) NOT NULL,LATEST_FILM varchar(20),Primary Key (ID));"
  con.query(sql);

  sql= "INSERT INTO Actors (NAME, LATEST_FILM)VALUES ('Prabhas', 'Salaar'),('Ram Charan', 'Game changer'),('Allu Arjun', 'Pushpa2');"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
      console.log("----------------------------------------");
  });

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

  sql = "INSERT INTO Actors (ID, NAME) VALUES (3, 'Fahad') ON DUPLICATE KEY UPDATE NAME = 'Fahad';"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("----------------------------------------");
  });

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

輸出

產生的輸出如下：

Connected!
----------------------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 1,
  info: 'Records: 3  Duplicates: 0  Warnings: 0',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
----------------------------------------
[
  { ID: 1, NAME: 'Prabhas', LATEST_FILM: 'Salaar' },
  { ID: 2, NAME: 'Ram Charan', LATEST_FILM: 'Game changer' },
  { ID: 3, NAME: 'Allu Arjun', LATEST_FILM: 'Pushpa2' }
]
----------------------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 2,
  insertId: 3,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
----------------------------------------
[
  { ID: 1, NAME: 'Prabhas', LATEST_FILM: 'Salaar' },
  { ID: 2, NAME: 'Ram Charan', LATEST_FILM: 'Game changer' },
  { ID: 3, NAME: 'Fahad', LATEST_FILM: 'Pushpa2' }
]    

public class InsertOnDuplicate {
  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 Actors";
            rs = st.executeQuery(sql);
            System.out.println("Table records before insert on duplicate key update: ");
            while(rs.next()) {
              String id = rs.getString("ID");
              String name = rs.getString("NAME");
              String latest_film = rs.getString("LATEST_FILM");
              System.out.println("Id: " + id + ", Name: " + name + ", Latest_film: " + latest_film);
            }
            //let use insert on duplicate update
            String sql1 = "INSERT INTO Actors (ID, NAME) VALUES (3, \"Ravi\") ON DUPLICATE KEY UPDATE NAME = \"Ravi\"";
            st.executeUpdate(sql1);
            System.out.println("Query insert on duplicate key update executed successfully....!");
            String sql2 = "SELECT * FROM Actors";
            rs = st.executeQuery(sql2);
            System.out.println("Table records after insert on duplicate update: ");
            while(rs.next()) {
              String id = rs.getString("ID");
              String name = rs.getString("NAME");
              String latest_film = rs.getString("LATEST_FILM");
              System.out.println("Id: " + id + ", Name: " + name + ", Latest_film: " + latest_film);
            }
            
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}  

輸出

得到的輸出如下所示：

Table records before insert on duplicate key update: 
Id: 1, Name: Prabhas, Latest_film: Salaar
Id: 2, Name: Ram Charan, Latest_film: Game changer
Id: 3, Name: Allu Arjun, Latest_film: Pushpa2
Query insert on duplicate key update executed successfully....!
Table records after insert on duplicate update: 
Id: 1, Name: Prabhas, Latest_film: Salaar
Id: 2, Name: Ram Charan, Latest_film: Game changer
Id: 3, Name: Ravi, Latest_film: Pushpa2      

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
insert_on_duplicate_key_update = "INSERT INTO tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (7, 'New Tutorial', 'John Doe', '2023-07-25') ON DUPLICATE KEY UPDATE tutorial_title='Updated Tutorial', tutorial_author='Jane Smith', submission_date='2023-07-28'"
cursorObj.execute(insert_on_duplicate_key_update)
connection.commit()
print("INSERT on duplicate key update query executed successfully.")
cursorObj.close()
connection.close()

輸出

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

INSERT on duplicate key update query executed successfully.