How can I do 'insert if not exists' in MySQL?

Practical ways to tackle in MySQL

INSERT IGNORE INTO for those peace-loving operations:

INSERT IGNORE INTO your_table (unique_column, column1)
VALUES ('proprietary_value','value1'); -- "Ignores" any party crashers. Keeps the operation running smoothly.


This takes the ostrich approach and avoids issues on the horizon, such as possible constraint violations. Let's not rock the boat, MySQL!

REPLACE INTO when you need to repaint the scenery:

REPLACE INTO your_table (unique_column, column1)
VALUES ('proprietary_value', 'value1'); -- "Out with the old, in with the new." MySQL style.


It's the little artist in MySQL that recreates the scene by zapping the incumbent record, and painting a fresh new one.

NOT EXISTS to smartly dodge already occupied space:

INSERT INTO your_table (unique_column, column1)
SELECT 'proprietary_value', 'value1'
FROM DUAL -- Like saying "Excuse me Oracle, can you visit the restroom?" to converse privately with MySQL.
WHERE NOT EXISTS (
    SELECT 1 FROM your_table WHERE unique_column = 'proprietary_value'
) LIMIT 1; -- All you need is one. Efficiency, thy name is LIMIT!


Here, LIMIT 1 is the bouncer at the admission, making sure the subquery slides home after finding the first duplicate.

MySQL's priority settings for 'importance' management:

INSERT LOW_PRIORITY INTO your_table (unique_column, column1)
VALUES ('proprietary_value','value1'); -- Worth inserting, but let's not rush.


or:

INSERT HIGH_PRIORITY INTO your_table (unique_column, column1)
VALUES ('proprietary_value','value1'); -- It's like shouting "Make way!! VIP coming through..."


These commands juggle the scheduling act in the MySQL sphere so you can fine-tune your performance.

The SQL - PHP symbiosis

Catering to data integrity via PHP

try {
    $sql = "INSERT IGNORE INTO your_table (unique_column, column1) VALUES (:proprietary_value, :value1)";
    $stmt = $pdo->prepare($sql); // PDO is like that friend who always has "a guy" for everything.
    $stmt->execute([':proprietary_value' => $proprietary_value, ':value1' => $value1]);
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage(); // We all have that one friend who never lets us forget our mistakes.
}


The 'one too many' situation: Batch Insertions!

INSERT INTO your_table (unique_column, column1)
VALUES 
  ('proprietary_value1', 'value1'),
  ('proprietary_value2', 'value2'), 
  ...
ON DUPLICATE KEY UPDATE column1 = VALUES(column1);  -- Making your own mark in the vast field of data.


Using transactions: Measure twice, cut once

START TRANSACTION;
-- Your conditional insert/update statements here
COMMIT; -- "Let's roll!"


In case of boo-boos:

ROLLBACK; -- "No, no, no. That didn't happen. We're good!"
