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"
});

 //Connecting to 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....!");
    });
//now let's insert some records
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...!");
    });
//now lets insert duplicate record with IGNORE keyword
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");
    });
//lets insert a duplicate record
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:\test1\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://: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
# Connecting to MySQL
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="tut"
)
# Creating a cursor object
cursorObj = con.cursor()
# Creating the table
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!")
# Inserting some records
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)
# Insert duplicate record with IGNORE keyword
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)
# Insert a duplicate record (this will throw an error)
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()
# Retrieving records
cursorObj.execute("SELECT * FROM person_tbl")
records = cursorObj.fetchall()
# Printing the records
print("Table records.")
for record in records:
    print(record)
# Closing the connection
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)