MySQL - Insert on Duplicate Key Update 语句

要通过 Node.js 程序将 MySQL 表中的重复行更新为新行，我们使用 DUPLICATE KEY UPDATE 和 INSERT 语句，并使用 mysql2 库中的 query() 函数 -

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

要通过 Java 程序将 MySQL 表中的重复行更新为新行，我们使用 DUPLICATE KEY UPDATE 和 INSERT 语句，并使用 JDBC 函数 executeUpdate() 作为 -

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

要通过 Python 程序在 MySQL 平板电脑中将重复行更新为新行，我们使用 DUPLICATE KEY UPDATE 和 INSERT 语句，并使用 MySQL Connector/Python 的 execute() 函数执行 -

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"
});
 //连接到 MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("----------------------------------------");
 //选择数据库
  sql = "USE TUTORIALS"
  con.query(sql);
 //创建表
  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://localhost: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);
            }
            //让重复更新时使用插入
            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
#建立连接
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.