MySQL:如果是新的则插入或如果为 NULL 则更新

Question

我在 MySQL 中有这个表，如果行不存在，我希望输入该表，否则仅当行为 NULL 时才更新。

我了解重复键更新和MySQL(如果有条件的话)，但无法弄清楚如何将它们放在一起。

mysql> mysql> describe patients;
+------------------------------+--------------+------+-----+---------+----------------+
| Field                        | Type         | Null | Key | Default | Extra          |
+------------------------------+--------------+------+-----+---------+----------------+
| patient_id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| patient_identifier           | varchar(64)  | NO   | MUL | NULL    |                |
| issuer_of_patient_identifier | int(11)      | YES  | MUL | NULL    |                |
| medical_record_locator       | varchar(64)  | YES  |     | NULL    |                |
| patient_name                 | varchar(128) | NO   | MUL | NULL    |                |
| birth_date                   | datetime     | NO   |     | NULL    |                |
| deceased_date                | datetime     | YES  |     | NULL    |                |
| gender                       | varchar(16)  | NO   |     | NULL    |                |
| ethnicity                    | varchar(45)  | YES  |     | NULL    |                |
| date_created                 | datetime     | NO   |     | NULL    |                |
| last_update_date             | datetime     | YES  |     | NULL    |                |
| last_updated_by              | varchar(128) | NO   |     | NULL    |                |
+------------------------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

按照戈登在评论中的要求添加了示例数据:

mysql> select * from patients \G;
*************************** 1. row ***************************
                  patient_id: 4909
          patient_identifier: TG18-2002
issuer_of_patient_identifier: 11
      medical_record_locator: MRL 1
                patient_name: AAPM^Test^Patterns
                  birth_date: 1992-07-04 01:02:03
               deceased_date: NULL
                      gender: O
                   ethnicity: North American
                date_created: 2018-01-24 21:32:02
            last_update_date: 2018-01-24 21:32:02
             last_updated_by: indexStore
1 row in set (0.00 sec)

示例 MySQL if 条件 if null then update:

Updating multiple columns in one go:
mysql> update test1234 set col1=IF(col1 IS NULL OR col1 = ' ',70,col1), 
   col2=IF(col2 IS NULL OR col2 = ' ','abracadabra',col2) where col3=8;

Answer 1

您可以在 ON DUPLICATE KEY 子句中使用 IF。

示例:

INSERT INTO table (col1, col2)
VALUES (x, y)
ON DUPLICATE KEY UPDATE
col1 = IF(col1 IS NULL, VALUES(col1), col1),
col2 = IF(col2 IS NULL, VALUES(col2), col2);