解决方案解决方案，以执行"INSERT ... ON DUPLICATE KEY UPDATE".在唯一键上具有空值 [英] Workaround solution to do a "INSERT...ON DUPLICATE KEY UPDATE" with null value on unique key
问题描述
首先，这是我的代码结构以及我要实现的目标:
我有这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/不执行任何操作/"，因为我需要我已插入/已经拥有的行的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索引一起使用?
我不能使用此解决方案(在这里).
我是SQL的初学者，所以请给我一些帮助，以实现此触发器.据我所知，该想法是使用触发器创建一些虚拟"列，如果"hour"为空，则在其中添加一些值，然后在我的UNIQUE索引中添加此虚拟列，以便ON DUPLICATE KEY起作用.
所以我的问题是:
- 如何在触发器中创建此虚拟列"?
- 如果我在不为NULL的情况下需要小时"值，该如何为我的UNIQUE索引使用此虚拟列"?
- 您有更好的主意吗?
我会回答自己的问题，但是如果你们有其他解决方案，我还是很感兴趣.该解决方案对我有用，因为我正在开发一个新项目，因此它不会真正影响我已经编写的代码.
要解决此问题，我将新列添加到"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")，则默认为小时"；如果默认为小时"为NULL，则默认为"1".
然后，我将此表上的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"，因此在这种情况下无需添加它.
我不知道这是否是拥有大量数据的BIG项目的最佳解决方案，但就我而言，这是解决我的问题的最简单方法.
To begin with, here is my code structure and what I want to achieve :
I have this 3 tables :
+----------------------+
| 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 |
+----------------------+
So one "slot_place_box" can have multiple "config" and one "config" can be on multiple "slot_place_box". I have some trigger on "slot_config" to update the field "slot_available" according to the config.
Now I have a PHP where I get some FORM data and build a SQL request which look like this (when I have my problem) :
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
The idea is to create a new "slot_place_box" + "slot_config" if I have no duplicate according to the new config I create before in my code
OR
If I already have a "slot_place_box" with same "id_place_box + date + hour" I only INSERT a new "slot_config" with the new_config id + the "slot_place_box" I already have.
I choose the "INSERT...ON DUPLICATE KEY /do nothing/" because I need the id of the row I INSERT / I already have.
I have an UNIQUE index key in "slot_place_box" for the fields "id_place_box + date + hour". My problem begin because "hour" can be an hour ("12:00:00" for example) but can be NULL too (= for me, when NULL it means "all day" of the "date"). When hour is NULL, my UNIQUE index doesn't work so the INSERT happens and I have duplicate in database.
So how can I make this "INSERT...ON DUPLICATE KEY UPDATE" works with an UNIQUE index with NULL value?
I can't use this solution (here) because I use MySQl 5.6 so I will try to make a trigger as someone suggest me in my last question (here).
I'm beginner in SQL so I'd like some help please to achieve this trigger please. The idea, if I understand well, is to create some "virtual" column with the trigger and add some value in it if "hour" is null + add this virtual column in my UNIQUE index so the ON DUPLICATE KEY will works.
So my questions are :
- How can I create this "virtual column" in a trigger?
- How can I use this "virtual column" for my UNIQUE index if I need the "hour" value when it's not NULL?
- Do you have a better idea?
I will anwser myself but if you guys have other solution I'm still interested to heard it. This solution works for me because I'm working on a new project so it don't really impact the code I already wrote.
To workaround this problem, I add a new column to my "slot_place_box" table :
+----------------------+
| SLOT_PLACE_BOX |
+----------------------+
| - id_slot_pb |
| - id_place_box |
| - date |
| - hour |
| - slot_available |
| - virtual_hour | <- new field
+----------------------+
This new field is a VARCHAR(10) equal to "hour" if not NULL (ex: "12:00:00") or equal to "1" by default or when "hour" is NULL.
Then I change my UNIQUE index on this table for "id_place_box + date + virtual_hour" so I will never get NULL value. Then I UPDATE the data I already have in my table :
UPDATE slot_place_box SET (virtual_hour = IF(hour is NULL, '1', hour);
// Without the safe UPDATE (I use MySQL WorkBench)
I change my PHP loop to build my request with the new column:
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);
';
}
}
I just added the fourth column with the "hour" value when "hour" is NOT NULL, by default "virtual_hour" is "1" so no need to add it in this case.
I don't know if it's the best solution for BIG project with lot of data, but in my case it was the easiest way to resolve my problem.
这篇关于解决方案解决方案，以执行"INSERT ... ON DUPLICATE KEY UPDATE".在唯一键上具有空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
