MySQL - 处理重复项

要通过 JavaScript 程序处理重复值，我们需要使用 mysql2 库的 query() 函数执行"INSERT IGNORE"语句，如下所示 -

sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')";
con.query(sql);

要通过 Java 程序处理重复值，我们需要使用 JDBC 函数 execute() 执行"INSERT IGNORE"语句，如下所示 -

String sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')";
statement.execute(sql);

要通过 Python 程序处理重复值，我们需要使用 MySQL Connector/Python 的 execute() 函数执行"INSERT IGNORE"语句，如下所示 -

sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')"
cursorObj.execute(sql)

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
//连接到 MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name))";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//现在让我们插入一些记录
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( 'John', 'Smith')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
//现在让我们使用 IGNORE 关键字插入重复记录
sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')";
    con.query(sql, function(err, result){
        console.log("Insert duplicate record with IGNORE keyword")
    if (err) throw err;
    console.log("Duplicate record inserted successfully with the help of IGNORE keyword");
    });
//插入一条重复记录
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( 'John', 'Smith')";
    con.query(sql, function(err, result){
        console.log("Insert duplicate record");
    if (err) throw err;
    console.log("You can't insert the duplicate record because columns are primary key");
    });
sql = "SELECT * FROM person_tbl";
con.query(sql, function(err, result){
    console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});   

输出

获得的输出如下所示 -

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Insert duplicate record with IGNORE keyword
Duplicate record inserted successfully with the help of IGNORE keyword
Insert duplicate record
Insert duplicate record
D:	est1\duplicate.js:43
    if (err) throw err;              ^

Error: Duplicate entry 'John-Smith' for key 'person_tbl.PRIMARY'

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HandleDuplicates {
   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...!");
            //create table
            String sql = "CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name))";
            st.execute(sql);
            System.out.println("Table created successfully....!");
            //let's insert some records
            String sql1 = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')";
            st.execute(sql1);
            System.out.println("Record inserted successfully...!");
            String sql2 = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')";
            st.execute(sql2);
            System.out.println("Duplicate record inserted successfully...!");
            //lets print the table records
            String sql3 = "SELECT * FROM PERSON_TBL";
            rs = st.executeQuery(sql3);
            System.out.println("Table records: ");
            while(rs.next()) {
               String fname = rs.getString("first_name");
               String lname = rs.getString("last_name");
               System.out.println("First name: " + fname + ", Last name: " + lname);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}   

输出

获得的输出如下所示 -

Table created successfully....!
Record inserted successfully...!
Duplicate record inserted successfully...!
Table records: 
First name: Thomas, Last name: Jay

import mysql.connector
# 连接到 MySQL
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="tut"
)
# 创建游标对象
cursorObj = con.cursor()
# 创建表
create_table_query = """
CREATE TABLE person_tbl (
    first_name CHAR(20) NOT NULL,
    last_name CHAR(20) NOT NULL,
    sex CHAR(10),
    PRIMARY KEY (last_name, first_name)
)
"""
cursorObj.execute(create_table_query)
print("Table 'person_tbl' is created successfully!")
# 插入一些记录
first_record = "INSERT INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')"
print("First record inserted successfully!")
cursorObj.execute(first_record)

second_record = "INSERT INTO person_tbl (last_name, first_name) VALUES( 'John', 'Smith')"
print("Second record inserted successfully!")
cursorObj.execute(second_record)
# 使用 IGNORE 关键字插入重复记录
sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas')"
print("Duplicate record inserted successfully with the help of IGNORE keyword")
cursorObj.execute(sql)
# 插入重复记录(这将引发错误)
try:
    cursorObj.execute("INSERT INTO person_tbl (last_name, first_name) VALUES( 'John', 'Smith')")
    print("Insert duplicate record")
except mysql.connector.Error as err:
    print("Insert duplicate record error:", err)
con.commit()
# 检索记录
cursorObj.execute("SELECT * FROM person_tbl")
records = cursorObj.fetchall()
# 打印记录
print("Table records.")
for record in records:
    print(record)
# 关闭连接
cursorObj.close()
con.close()   

输出

获得的输出如下所示 -

First record inserted successfully!
Second record inserted successfully!
Duplicate record inserted successfully with the help of IGNORE keyword
Insert duplicate record error: 1062 (23000): Duplicate entry 'John-Smith' for key 'person_tbl.PRIMARY'
Table records.
('Thomas', 'Jay', None)
('Smith', 'John', None)