I read some threads about this topic and tried so much but my code does not work. If a dataset already exists in my table, i do not want to insert it twice.
My table "WORD" with some example data (id is auto increment).
id | customer_id | category_id | word | comment
1 3 5 life null
2 5 5 motor tbd
3 null 2 Day week
insert into word (customer_id, category_id, word)
select * from (select null, 2, 'Day') as tmp
where not exists (select * from word where customer_id=null and category_id=2 and word='Day')
select * from word where customer_id=null
select * from word where customer_id IS NULL
See this site in the manual: http://dev.mysql.com/doc/refman/5.7/en/working-with-null.html
A way to dodge the workaround in your statement would be to set an unique index on those columns, if your usecases allow the constraint. http://dev.mysql.com/doc/refman/5.7/en/create-index.html
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.