今天写个小程序需要用到mysql,需求就是要求插入数据，但该数据不能已存在于表中，否则就不插入。
比如我需要插入question="aaa",correctAnswer="bbb"的数据，代码如下：
|
1
2
3
4
5
6
7
8
|
INSERT
INTO
questions
(question,correctAnswer)
SELECT
'aaa'
,
'bbb'
FROM
dual
where
not
exists(
SELECT
*
FROM
questions
where
question=
'aaa'
and
correctAnswer=
'bbb'
)
|
|
1
|
<span class=
"span4br"
></span>
|
其中"dual"为虚表，用于不提供实际表名查询数据。
当然，如果已经插入了若干条重复数据，则可以这么删除:
|
1
2
3
4
5
6
7
8
9
10
|
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
|
|
1
|
<span class=
"span4br"
></span>
|
