MySQL (Auto Delete Old Rows)
Moderator: General Moderators
MySQL (Auto Delete Old Rows)
Lets say I have a table with a column id and lastuserlogin column and on every login a user name is inserted into the table.
Such as $query = "INSERT INTO table (lastuserlogin) VALUES ('$username')";
Is it possible to add something like [if #row>100 DELETE oldestrows until #rows==100] in the query?
Such as $query = "INSERT INTO table (lastuserlogin) VALUES ('$username')";
Is it possible to add something like [if #row>100 DELETE oldestrows until #rows==100] in the query?
Re: MySQL (Auto Delete Old Rows)
Not a simply as you put it. how would it know what to delete?
Usually when auto purging data, I have a field that is timestamp and then once a day (or more, depending on activity), execute a PHP script that does the following to remove anything over 24 hours old.
Note, if you are running the above, and the cron is set to run just once a day, keep in mind that right before the cron runs, you can have entries almost 48 hours old.
-Greg
Usually when auto purging data, I have a field that is timestamp and then once a day (or more, depending on activity), execute a PHP script that does the following to remove anything over 24 hours old.
Code: Select all
DELETE FROM `table` WHERE `timestampfield` < DATE_SUB(NOW(), INTERVAL 24 HOUR)-Greg
Re: MySQL (Auto Delete Old Rows)
By examining the id column (I have a primary key set on column id w/ auto increment)how would it know what to delete?
I'd really rather not use cron jobs. I know how to code this into the PHP script, but I just thought I would check if MySQL had a way of doing this within a query. (Since it usually does after I program lines and lines of code to do the same thing a well constructed query could do.)
Re: MySQL (Auto Delete Old Rows)
Well then you could set up a trigger on the server, so that every time a record is inserted, it does the following (you'd need to look how to program the stored procedure and trigger, as I don't do them enough to give it off of the top of my head:
DELETE FROM `table` WHERE `id` < (SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT 100,1)
This is depending on that there will always be a 100th record. Since you are doing it as a stored procedure, then you would be better to break it up into a separate SELECT statement, and then only do the DELETE statement if the result is numeric (or some other type check to make sure there was a 100th row)
-Greg
DELETE FROM `table` WHERE `id` < (SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT 100,1)
This is depending on that there will always be a 100th record. Since you are doing it as a stored procedure, then you would be better to break it up into a separate SELECT statement, and then only do the DELETE statement if the result is numeric (or some other type check to make sure there was a 100th row)
-Greg
Re: MySQL (Auto Delete Old Rows)
i'm not too good with MySQL so the following is probably wrong somewhere. What do you think of something like this?
Code: Select all
DELETE FROM `table` WHERE `id` < (SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT (COUNT(`id`) - (COUNT(`id`) - 100)), 1)Re: MySQL (Auto Delete Old Rows)
Well, COUNT(id) - (COUNT(id) - 100) will always give you 100, so there is no need to do that.
Re: MySQL (Auto Delete Old Rows)
I don't think my math is wrong:Well, COUNT(id) - (COUNT(id) - 100) will always give you 100, so there is no need to do that.
Code: Select all
z = (r-(r-100))
r = 50
(r-(r-100)) == (50 - (50-100)) == (50 - (-50)) == -100
r = 100
(r-(r-100)) == (100 - (100-100)) == (100 - (000)) == 100
r = 200
(r-(r-100)) == (200 - (200-100)) == (200 - (100)) == 100
r = 300
(r-(r-100)) == (300 - (300-100)) == (300 - (200)) == 100
Code: Select all
DELETE FROM `table`
WHERE `id` <
(SELECT `id`
FROM `table`
ORDER BY `id` DESC
LIMIT IF(COUNT(`id`) >= 100, (COUNT(`id`) - (COUNT(`id`) - 100)), IF(COUNT(`id`) < 100, 0, (`id` - 1))), 1)
Re: MySQL (Auto Delete Old Rows)
This is right.twinedev wrote:Well, COUNT(id) - (COUNT(id) - 100) will always give you 100, so there is no need to do that.
Your math is wrong. 50 - (-50) = 100Hermit TL wrote:I don't think my math is wrong:Code: Select all
(r-(r-100)) == (50 - (50-100)) == (50 - (-50)) == -100
Re: MySQL (Auto Delete Old Rows)
So it is... I'm starting over. Break time!Hermit TL wrote:I don't think my math is wrong:
Syntax: [ Download ] [ Hide ]
Syntax: [ Download ] [ Show ]
1. (r-(r-100)) == (50 - (50-100)) == (50 - (-50)) == -100
Your math is wrong. 50 - (-50) = 100
Re: MySQL (Auto Delete Old Rows)
I have no idea what I'm doing.
I'm going to try this, and... see what happens.
I'm going to try this, and... see what happens.
Code: Select all
DELETE * FROM `table` WHERE `id` < ((((SELECT COUNT(`id`) FROM table`) - 100) + (SELECT `id` FROM `table` ORDER BY ASC LIMIT 1)) + 1);Re: MySQL (Auto Delete Old Rows)
Code: Select all
DELETE * FROM `table`
WHERE `id` < ((((SELECT COUNT(`id`) FROM TABLE `table` LIMIT 1) - 100) + (SELECT `id` FROM `table` ORDER BY `id` ASC LIMIT 1)) + 1);
Re: MySQL (Auto Delete Old Rows)
So what happens when you try the original one I gave you?
-Greg
Code: Select all
DELETE FROM `table` WHERE `id` < (SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT 100,1)Is invalid syntax, there should not be * in there.DELETE * FROM `table`
-Greg
Re: MySQL (Auto Delete Old Rows)
Thanks, it's actually running now. However. I'm getting Error 1093: "You can't specify target table `table` for update in FROM clause."
Your code returns the same error as well.
What does it want?
Your code returns the same error as well.
What does it want?
Re: MySQL (Auto Delete Old Rows)
I was wondering if it might complain, the issue is that it doesn't like you selecting data from same table you are trying to delete from (as one action may affect another). IMO shouldn't matter you'd think it is selecting info then deleting, but it gets picky (for valid reasons behind the scenes I am sure). So with that you would probably need to save it as a stored procedure on the server that does them in two steps.
Re: MySQL (Auto Delete Old Rows)
So, like I said, don't do it much, but something like this might work:
Then when you want to run it, you just need to do CALL TruncateTable(); I think
Definitely would need to be tested
Code: Select all
DELIMITER $$
CREATE PROCEDURE `TruncateTable`()
BEGIN
DECLARE tr INT DEFAULT 0;
SELECT COUNT(`id`) INTO tr FROM `table`;
IF tr > 100 THEN
SELECT `id` INTO tr FROM `table` ORDER BY `id` DESC LIMIT 100,1;
DELETE FROM `table` WHERE `id` < tr;
END IF;
END$$
DELIMITER ;Definitely would need to be tested