0

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?

wp78de
  • 18,207
  • 7
  • 43
  • 71
Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
  • The date is different so it's not a duplicate. – tkausl Dec 05 '17 at 17:28
  • Yeah I know in my script I have no duplicate, but in database I can have some "slot_place_box" with the same "id_place_box", "date", "hour" so I have duplicate. When I have one, I want to do nothing and just continue my script. – Mickaël Leger Dec 05 '17 at 17:33
  • 2
    *"The fact that I have a "NULL" value for "hour" maybe create some trouble with my UNIQUE indexe..."*. This. If a tuple contains a `null`, you can insert as many "duplicates" of this as you want. This is the correct behaviour according to the sql standard. See e.g. [here](https://stackoverflow.com/q/3712222). For a workaround (other than redesigning your data model or doing the tests manually) you can try something like [this](https://stackoverflow.com/a/42291845) (it works with triggers as well, you do not need to use generated columns/mysql 5.7+) – Solarflare Dec 05 '17 at 17:57
  • Ok thanks for the anwser and the links I will try to solve my problem with this :) – Mickaël Leger Dec 06 '17 at 08:10
  • The workaround solution is for MySQL 5.7, we use the 5.6...can't create a virtual column so I will have to change my request or my table logic or all my scripts using this table :/ – Mickaël Leger Dec 06 '17 at 09:26
  • As I mentioned: even though the solution in the link uses generated columns (which require 5.7), you can do exactly the same with triggers, see e.g. [here](https://stackoverflow.com/a/27053371/6248528) (just use a different value in `ifnull(xxx,0)`, e.g. -1 (if you use signed int), as "0" is a valid hour). A generated column is just a little more comfortable (one line of code instead of 10). – Solarflare Dec 06 '17 at 09:42

1 Answers1

0

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".

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
  • You can use triggers to do the same thing as generated columns, see my comment. – Solarflare Dec 06 '17 at 09:45
  • The problem is that I need the last id I insert (if no duplicate) or the id of the duplicate row because I use it after. Do you think I can do a test for example : "SEt has_data = (SELECT COUNT(*) *my request*); IF has_data = 0 THEN INSERT" ? – Mickaël Leger Dec 06 '17 at 09:58
  • @Solarflare : I think about the trigger solution and the idea should be to make a "BEFORE INSERT" trigger that test if I already have a row, and if I already have a row I don't INSERT. A solution [here](https://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions/22489342#22489342). But if I create a sql error, will my script continue? – Mickaël Leger Dec 06 '17 at 10:47
  • No, the idea is a different one. It actually works exactly the same way as the generated column, so try it, even if you don't believe it at first. The 2 triggers (especially the `before insert` trigger) will result in a "unique key violation".You are not supposed to check in the trigger if the row exists. Just set the dummy column to a non-null value and the unique key on it does the rest (as if `null` were a "normal" value - since you replaced it with a normal value in that column). "On duplicate key update" catches that key violation error and does whatever you do in the update part. – Solarflare Dec 06 '17 at 12:25
  • Ok I think I see the idea, but I'm beginner so I'm not sure about how I can do it exactly. I will try but I think I will open a new question to be sure to understand well and stay on topic – Mickaël Leger Dec 06 '17 at 13:27
  • @Solarflare If you want to anwser, I gave more explaination [here](https://stackoverflow.com/questions/47676253/workaround-solution-to-do-a-insert-on-duplicate-key-update-with-null-value-o) – Mickaël Leger Dec 06 '17 at 14:08