php - 在唯一键上使用空值执行 "INSERT...ON DUPLICATE KEY UPDATE"的变通解决方案

Question

首先，这是我的代码结构和我想要实现的目标:

我有这 3 个表:

+----------------------+
| CONFIG_CAMPAIGN      |
+----------------------+
| - id_config          | 
| // other fields      |
+----------------------+

+----------------------+
| SLOT_CONFIG          |
+----------------------+
| - id_slot_config     | 
| - id_config          |
| - id_slot_pb         |
+----------------------+

+----------------------+
| SLOT_PLACE_BOX       |
+----------------------+
| - id_slot_pb         | 
| - id_place_box       |
| - date               |
| - hour               |
| - slot_available     |
+----------------------+

所以一个“slot_place_box”可以有多个“config”，一个“config”可以在多个“slot_place_box”上。我在“slot_config”上有一些触发器根据配置更新字段“slot_available”。

现在我有一个 PHP，我可以从中获取一些 FORM 数据并构建一个如下所示的 SQL 请求(当我遇到问题时):

INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-10', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box;
SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-10' AND SPB.hour IS NULL);
INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id);

INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-11', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box;
SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-11' AND SPB.hour IS NULL);
INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id);

INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-12', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box;
SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-12' AND SPB.hour IS NULL);
INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id);

// and so on

我的想法是创建一个新的“slot_place_box”+“slot_config”，如果我没有根据我之前在代码中创建的新配置复制的话

或

如果我已经有一个具有相同“id_place_box + date + hour”的“slot_place_box”，我只插入一个新的“slot_config”，其中包含 new_config id + 我已有的“slot_place_box”。

我选择“INSERT...ON DUPLICATE KEY/do nothing/”因为我需要我插入的行的 ID/我已经有了。

我在“slot_place_box”中为字段“id_place_box + date + hour”有一个唯一索引键。我的问题开始是因为“小时”可以是一个小时(例如“12:00:00”)但也可以是 NULL(= 对我来说，当 NULL 时它意味着“日期”的“全天”)。当 hour 为 NULL 时，我的 UNIQUE 索引不起作用，因此 INSERT 发生并且我在数据库中有重复项。

那么我怎样才能使这个“INSERT...ON DUPLICATE KEY UPDATE”与具有 NULL 值的 UNIQUE 索引一起工作？

我不能使用此解决方案 (here)，因为我使用 MySQl 5.6，所以我将尝试按照有人在我上一个问题 (here) 中建议的方式创建触发器。

我是 SQL 初学者，所以我需要一些帮助来实现这个触发器。如果我理解得很好，这个想法是用触发器创建一些“虚拟”列，如果“小时”为空，则在其中添加一些值 + 在我的 UNIQUE 索引中添加这个虚拟列，这样 ON DUPLICATE KEY 就可以工作了。

所以我的问题是:

• 如何在触发器中创建这个“虚拟列”？
• 如果我需要非 NULL 的“小时”值，我如何将这个“虚拟列”用于我的 UNIQUE 索引？
• 你有更好的主意吗？

Answer 1

我会自己回答，但如果你们有其他解决方案，我仍然很想听听。这个解决方案对我有用，因为我正在处理一个新项目，所以它不会真正影响我已经编写的代码。

为了解决这个问题，我在“slot_place_box”表中添加了一个新列:

+----------------------+
| SLOT_PLACE_BOX       |
+----------------------+
| - id_slot_pb         | 
| - id_place_box       |
| - date               |
| - hour               |
| - slot_available     |
| - virtual_hour       | <- new field
+----------------------+

这个新字段是一个 VARCHAR(10)，如果不为 NULL(例如:“12:00:00”)，则等于“hour”，或者默认情况下等于“1”，或者当“hour”为 NULL 时。

然后我将此表上的 UNIQUE 索引更改为“id_place_box + date + virtual_hour”，这样我就永远不会得到 NULL 值。然后我更新表中已有的数据:

UPDATE slot_place_box SET (virtual_hour = IF(hour is NULL, '1', hour);
// Without the safe UPDATE (I use MySQL WorkBench)

我更改我的 PHP 循环以使用新列构建我的请求:

 foreach($SPB_data_array as $index => $SPB_data) {
    $current_id_pb = $SPB_data['id_pb'];
    $current_date  = $SPB_data['date'];

    if (!empty($SPB_data['hour'])) {
        $current_hour = $SPB_data['hour'];
        $sql_slot_pb .= 'INSERT INTO media.slot_place_box (id_place_box, date, hour, virtual_hour) VALUES ('. $current_id_pb .', \''. $current_date .'\', \''. $current_hour .'\', \''. $current_hour .'\')
                            ON DUPLICATE KEY UPDATE id_place_box=id_place_box;
                         SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB
                            WHERE SPB.id_place_box = '. $current_id_pb .' AND SPB.date = \''. $current_date .'\' AND SPB.hour = \''. $current_hour .'\');
                         INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (:new_config_id, @id);
                         ';
    } else {
        $sql_slot_pb .= 'INSERT INTO media.slot_place_box (id_place_box, date, hour) VALUES ('. $current_id_pb .', \''. $current_date .'\', NULL)
                            ON DUPLICATE KEY UPDATE id_place_box=id_place_box;
                         SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB
                            WHERE SPB.id_place_box = '. $current_id_pb .' AND SPB.date = \''. $current_date .'\' AND SPB.hour IS NULL);
                         INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (:new_config_id, @id);
                         ';
    }
}

当“hour”不为 NULL 时，我刚刚添加了带有“hour”值的第四列，默认情况下“virtual_hour”为“1”，因此在这种情况下无需添加它。

我不知道这是否是具有大量数据的大型项目的最佳解决方案，但就我而言，这是解决我的问题的最简单方法。