Insert if not exist MySQL?

Question

MySQL

mysqli_query($connect, "INSERT INTO wishlist (user_id, product_id) VALUES (12, 3) WHERE NOT EXISTS (SELECT id FROM wishlist WHERE user_id = 12 AND product_id = 3)");

Nothing is added to the wishlist table even though there is no row with user_id = 12 and product_id = 3

Answer 1

insert with WHERE do not seem to coexist. You can either use if or make this key pair unique and use insert on duplicate key ignore.

Answer 2

insert into wishlist(user_id, product_id)
select t.user_id, t.product_id
from (
    select 12 as user_id, 3 as product_id
) t
left outer join wishlist w on t.product_id = w.product_id and t.user_id = w.user_id
where w.user_id is null and w.product_id is null

If there is a lot of data to load, then instead of "immediate" create a temporary table (create temporary table) and load through it:

start transaction;

create temporary table tempWishlist as select * from wishlist where 0 = 1;

# temporary table load:
# insert into tempWishlist(...) values(...)

insert into wishlist(user_id, product_id)
select t.user_id, t.product_id
from tempWishlist t
left outer join wishlist w on t.product_id = w.product_id and t.user_id = w.user_id
where w.user_id is null and w.product_id is null

commit;