PDO - 根据3个值检测到重复时插入新行

Question

Currently I run a SELECT query on a table and if the num_rows is zero i then run a new function that INSERTs the record into the table. This seems very inneficient and I am struggling to find a good solution.

Current set up (simplified): Select Function -

$sql = "SELECT * FROM myTable WHERE col1 = ".$val1." AND col4 = ".$val4." AND col6 = ".$val6
// rest of code returns $numRows variable 

If no $numRows is 0 then run a fresh query inserting the new record.

if($numRows == 0) {
    try {
        $dbh = $this->_dbSite;
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $dbh->prepare("INSERT INTO myTable (col1, col2, col3, col4, col4, col6, col7) VALUES (:val1, :val2, :val3, :val4, :val5, :val6, :val7");
        $stmt->bindParam(':val1', $val1, PDO::PARAM_STR);
        $stmt->bindParam(':val2', $val2, PDO::PARAM_STR);
        $stmt->bindParam(':val3', $val3, PDO::PARAM_STR);
        $stmt->bindParam(':val4', $val4, PDO::PARAM_STR);
        $stmt->bindParam(':val5', $val5, PDO::PARAM_STR);
        $stmt->bindParam(':val6', $val6, PDO::PARAM_STR);
        $stmt->bindParam(':val7', $val7, PDO::PARAM_STR);
        $stmt->execute();
    } catch (PDOException $e) {
        return $e->getMessage();
    }
} else {
    $message = "Sorry this record already exists";
}

Table structure:

col1   | col2 | col3 | col4 | col5 | col6 | col7
________________________________________________
user1    abc    sol    red    doo     3a    def
user2    abc    ast    Blue   doo     4a    def
user1    abc    ast    blue   doo     3a    def
user4    abc    ast    red    doo     6a    def
user1    abc    ast    Green  doo     3a    def
user2    abc    ast    red    doo     7a    def
user1    abc    ast    red    doo     3a    def

The above example should be avoided as the first and last rows are identical in terms of col1,col4 and col6

This does not seem the most efficient way of doing this. Has anybody encountered this before? Any advice would be greatly appreciated.

Answer 1

If the combination of col1, col4, and col6 is intended to be unique -- meaning that it should only appear once, then you can do this with insert on duplicate key update and a unique index:

CREATE INDEX idx_myTable_col1_col4_col6 on (mytable, col1, col4, col6)

INSERT INTO myTable(col1, col2, col3, col4, col4, col6, col7)
    VALUES (:val1, :val2, :val3, :val4, :val5, :val6, :val7)
    ON DUPLICATE KEY UPDATE col1 = VALUES(col1);

The update part is a no-op -- it just prevents an error from returning.

You can also do:

INSERT INTO myTable(col1, col2, col3, col4, col4, col6, col7)
    SELECT :val1, :val2, :val3, :val4, :val5, :val6, :val7
    FROM dual
    WHERE NOT EXISTS (SELECT 1
                      FROM myTable t2
                      WHERE t2.col1 = :val1 and t2.col4 = :val4 and t2.col6 = :val6
                     );

This will work regardless of whether or not the columns need to be unique.