mysql仅在不存在时插入不起作用 [英] mysql insert only if not exists doesn't work

问题描述

我阅读了一些有关此主题的主题并尝试了很多，但我的代码不起作用.如果数据集已存在于我的表中，我不想将其插入两次.

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.

我的表格WORD"带有一些示例数据(id 是自动递增的).

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

我只想检查 customer_id、category_id、word.如果新行具有完全相同的 customer_id、category_id 和 word，我不想插入它.评论和 ID 无关紧要.

I ONLY want to check customer_id, category_id, word. If the new row has exactly same customer_id, category_id and word, I don't want to insert it. comment and id dosn't matter.

我的代码:

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')
limit 1

这是插入的:(他正在插入它，尽管我有一行 (id = 3) 为 null/2/Day.新插入的有评论空，其他有评论周，但这没关系.

THIS is inserted :( He is inserting it, though I have a row (id =3) with null/2/Day. The new inserted one has comment null, the othe hast comment week, but this doesn't matter.

我的错误在哪里?

推荐答案

select * from word where customer_id=null

应该

select * from word where customer_id IS NULL

在手册中查看此站点:http:///dev.mysql.com/doc/refman/5.7/en/working-with-null.html

如果您的用例允许约束，则在您的语句中避开解决方法的一种方法是在这些列上设置唯一索引.http://dev.mysql.com/doc/refman/5.7/en/create-index.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

UNIQUE 索引创建一个约束，使得索引中的所有值必须是不同的.如果您尝试添加带有匹配现有行的键值.对于所有引擎，一个独特的index 允许包含 NULL 的列有多个 NULL 值.如果为 UNIQUE 索引中的列指定前缀值，则列值在前缀内必须是唯一的.

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.

这篇关于mysql仅在不存在时插入不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！