Create a stored procedure in mysql which deletes the row if exists, if not insert an value
I have written the below procedure but when it executes the table always keep only one data in it and rest is deleted whenever a call made to the Stored Procedure.
So finally only the latest value is stored and the rest all gets deleted, so i end up in getting only one result data from my table .
Is there any thing wrong i am doing in the below procedure?
CREATE PROCEDURE `PersonVisitInfo`(personid varchar(254),visits int )
BEGIN
declare _personid varchar(254);
declare _visit_count int;
declare oldest_item varchar(254);
set _personid = personid;
set _visit_count = visits;
delete from personData where personid = _personid ;
if (select count(*) from personData where personid = _personid) >= _visit_count then
SELECT id into oldest_item FROM personData WHERE personid = _personid ORDER BY lastvisitdate LIMIT 1;
DELETE开发者_运维百科 FROM personData WHERE id = oldest_item;
end if;
insert into personData(id, personid, lastvisitdate) values(UUID(), _personid, now());
END$$
You need to remove this line:
delete from personData where personid = _personid ;
This removes all the person's records from the table, meaning that the following if statement will always be false, and the last line will insert the new record - which will be the only record for that person.
delete from personData where personid = _personid ;
if (select count(*) from personData where personid = _personid) >= _visit_count
But you've just deleted all the rows - so the 'if' statement will never be true.
Leaving that aside, why not just have a unique key on personid and use 'REPLACE' or 'INSERT....ON DUPLICATE KEY UPDATE' instead:
精彩评论