我现在正尝试创建一个查询,检查表中是否存在记录,然后不执行其他操作插入记录。我已经看到了这个任务的多个例子。从这些例子中,我创建了一个查询。
查询:
CREATE PROCEDURE Answer ()
IF EXISTS
(SELECT * FROM answer WHERE a = 'a' && b = 'b' && e = 'e ' && timestamp = '1423828119' )
THEN
BEGIN
SELECT 'This record already exists!'
END
IF ELSE
BEGIN
SELECT 'Record Added'
INSERT INTO answer(a, b, c, d, e, timestamp)
VALUES ('a','b','c','d','e','1423828119')
END
错误:syntax to use near ' 'IF ELSE BEGIN SELECT 'Record Added' INSERT INTO answer(a, b,'
不是我在mysql中有一个if条件的错误。请引导我朝正确的方向走。
使用EXISTS
检查查询是否返回任何内容:
if exists(
select *
from answer
where a = 'a' and b = 'b' and e = 'e' and timestamp = '1423828119'
) then begin
select 'This record already exists!'
end else begin
select 'Record Added'
insert into answer(a, b, c, d, e, $timestamp)
values ('a','b','c','d','e ','1423828119')
end
在大多数DBMS中都有MERGE
语句,但MySQL不支持它。
以下查询不需要存储过程:
INSERT INTO answer(a, b, c, d, e, timestamp)
SELECT 'a','b','c','d','e','1423828119'
WHERE NOT EXISTS
(
SELECT * FROM answer
WHERE a = 'a' AND b = 'b' AND e = 'e' AND timestamp = '1423828119
);
您应该使用WHERE a = 'a' AND b = 'b' AND e = 'e' AND timestamp = '1423828119'
而不是WHERE a = 'a' && b = 'b' && e = 'e' && timestamp = '1423828119'
(请参阅而不是&&i使用AND)
你也可以使用IF EXIST
而不是if TRUE
,比如:
IF EXISTS (select ...) THEN
select ...;
ELSE
...
END IF;