MySQL实现不存在则插入或仅更新NULL字段的技术问询

Got it, let's break this down. You want to insert a new patient record if it doesn't exist, and if the record already exists (based on a unique identifier), only update the fields that are currently NULL in the existing row. Here's how to combine ON DUPLICATE KEY UPDATE with MySQL's IF() function to make this work perfectly.

First: Ensure a Unique Constraint #

First things first—ON DUPLICATE KEY UPDATE only triggers when a primary key or unique key is duplicated. Looking at your table, patient_id is an auto-increment primary key, so we can't use that for duplicate checks (since we won't know it for new inserts). Instead, we'll use patient_identifier (which is non-null) by adding a unique index to it:

ALTER TABLE patients ADD UNIQUE INDEX idx_unique_patient_identifier (patient_identifier);

This tells MySQL to treat patient_identifier as a unique value, so inserting a duplicate will trigger the update logic.

The Complete Insert/Update Query #

Here's the full query that does exactly what you need. We'll use IF() to check if the existing field is NULL—if it is, we replace it with the new value; if not, we leave it as-is.

INSERT INTO patients (
    patient_identifier,
    issuer_of_patient_identifier,
    medical_record_locator,
    patient_name,
    birth_date,
    deceased_date,
    gender,
    ethnicity,
    date_created,
    last_update_date,
    last_updated_by
) VALUES (
    'TG18-2002', -- The unique identifier we're checking against
    12, -- New issuer value (won't update if existing is not NULL)
    'MRL 2', -- New medical record locator (won't update if existing is not NULL)
    'AAPM^Test^Patterns',
    '1992-07-04 01:02:03',
    '2024-05-20 00:00:00', -- New deceased date (will update since existing is NULL)
    'O',
    'European', -- New ethnicity (won't update if existing is not NULL)
    NOW(),
    NOW(),
    'admin'
)
ON DUPLICATE KEY UPDATE
    -- Update only if existing field is NULL
    issuer_of_patient_identifier = IF(issuer_of_patient_identifier IS NULL, VALUES(issuer_of_patient_identifier), issuer_of_patient_identifier),
    medical_record_locator = IF(medical_record_locator IS NULL, VALUES(medical_record_locator), medical_record_locator),
    deceased_date = IF(deceased_date IS NULL, VALUES(deceased_date), deceased_date),
    ethnicity = IF(ethnicity IS NULL, VALUES(ethnicity), ethnicity),
    last_update_date = IF(last_update_date IS NULL, VALUES(last_update_date), last_update_date),
    -- For non-null fields like last_updated_by, we can always update it to track who made the change
    last_updated_by = VALUES(last_updated_by);

How It Works #

Let's walk through this with your sample data:

• Your existing record has deceased_date = NULL—so this query will update that field to '2024-05-20 00:00:00'.
• Fields like issuer_of_patient_identifier (existing value 11) and ethnicity (existing value 'North American') are non-NULL, so the query leaves them unchanged, ignoring the new values we provided.
• VALUES(column_name) is a handy way to reference the value we tried to insert for that column, so we don't have to repeat the value in the update clause.

Notes #

• If you need to check for duplicates based on multiple fields (e.g., patient_identifier + issuer_of_patient_identifier), create a composite unique index instead:

  ALTER TABLE patients ADD UNIQUE INDEX idx_unique_patient_issuer (patient_identifier, issuer_of_patient_identifier);
  

• For non-null fields like patient_name or birth_date, you don't need to include them in the ON DUPLICATE KEY UPDATE clause unless you want to override them (but your requirement says only update NULL fields, so we can skip them).

内容的提问来源于stack exchange，提问作者RajSanpui