MySQL不插入重复数据与删除重复数据

  • 2014-06-07
  • 0
  • 3

今天写个python小程序需要用到mysql,需求就是要求插入数据,但该数据不能已存在于表中,否则就不插入。

比如我需要插入question="aaa",correctAnswer="bbb"的数据,代码如下:

INSERT INTO questions
(question,correctAnswer) 
SELECT 'aaa','bbb' FROM dual 
where not exists(
SELECT * FROM questions 
where question='aaa'
and correctAnswer='bbb'
)

其中"dual"为虚表,用于不提供实际表名查询数据。

当然,如果已经插入了若干条重复数据,则可以这么删除:

DELETE FROM a USING questions AS a,
(
SELECT * 
FROM questions
GROUP BY question, correctAnswer
HAVING COUNT( * ) >1
) AS b
WHERE a.question = b.question
AND a.correctAnswer = b.correctAnswer
AND a.questionId > b.questionId

小记于此 备查

上一篇:    下一篇:

me@ccc5.cc - 衫小寨