“INSERT...ON DUPLICATE KEY UPDATE id=id"仍然插入重复 [英] "INSERT...ON DUPLICATE KEY UPDATE id=id" still inserts on duplicate

问题描述

我有一个表名slot_place_box".它看起来像这样:

• id_slot_pb(主键)
• id_place_box (INT)
• 日期(日期)
• 小时 (HOUR)
• slot_available (INT)

我有(id_place_box + 日期 + 小时)的唯一索引.

在 PHP 中，我正在构建一个 SQL 请求，它看起来像这样:

$sql_request = "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 作为 SPB WHERESPB.id_place_box = 32 AND SPB.date = '2017-12-10' AND SPB.hour IS NULL);插入 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);插入 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);插入 media.slot_config (id_config, id_slot_pb) VALUES (125, @id);//等等";

我的问题是我的INSERT...ON DUPLICATE KEY UPDATE"仍然插入一个新行，当我有一个重复的键(=数据库中的另一个slot_place_box"具有相同的id_place_box + date + hours"，所以我的@id 得到多个结果和脚本错误.它只是在我的表中为我的第一个插入"添加了一行，之后(当我有重复的行时)什么也不做.

现在这是我的线索:

• 我为id_config + date + hours"的组合添加了UNIQUE"索引，对于ON DUPLICATE KEY"是否足够，或者我绝对需要PRIMARY KEY?
• 也许我的字段名称日期"和小时"有问题?
• 我的小时"值为NULL"这一事实可能会给我的 UNIQUE 索引带来一些麻烦...

你知道为什么吗?

解决方案

正如@Solarflare 在评论中所说，我的问题是因为我的字段hour"的NULL"值(参见 此处).

我无法使用他向我展示的解决方法(此处) 因为我使用 MySQL 5.6 而不是 5.7.

我将不得不稍微更改我的请求或更改我的表逻辑或在HOUR"中放置一个值(例如负数)，但我将不得不更改我使用此NULL"的部分代码.

I have a table name "slot_place_box". It looks like this :

• id_slot_pb (PRIMARY KEY)
• id_place_box (INT)
• date (DATE)
• hour (HOUR)
• slot_available (INT)

I have an UNIQUE index for (id_place_box + date + hour).

In PHP I'm building an SQL request, it looks like this :

$sql_request = "
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); 

//etc

";

My problem is that my "INSERT...ON DUPLICATE KEY UPDATE" still insert a new line when I have a duplicate key (= another "slot_place_box" in the database with same "id_place_box + date + hour", so my @id get more than one result and the script bug. It just adds a row in my table for my first "INSERT" and does nothing after (when I have duplicate row).

Right now this is the clues I have :

• I added a "UNIQUE" index for the combination of "id_config + date + hour", is it enough for the "ON DUPLICATE KEY" or I absolutely need the PRIMARY KEY?
• Maybe I have trouble with my field name "date" and "hour"?
• The fact that I have a "NULL" value for "hour" maybe create some trouble with my UNIQUE indexes...

Do you have an idea why please?

解决方案

As @Solarflare said in comments, my problem is because of the "NULL" value of my field "hour" (see here).

I can't use the workaround solution he shows me (here) because I use MySQL 5.6 and not 5.7.

I will have to change my request a little OR change my table logic OR put a value in "HOUR" (negative for example) but I will have to change part of my code where I used this "NULL".

这篇关于“INSERT...ON DUPLICATE KEY UPDATE id=id"仍然插入重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！