Instantly share code, notes, and snippets.

Embed
What would you like to do?
Log rotation in MySQL table
DELETE FROM logs WHERE id IN (select id from (select id FROM log ORDER BY id DESC LIMIT 1000, 500) x)
CREATE TABLE `log` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`level` TINYINT(4) NOT NULL,
`content` TEXT NOT NULL,
`date` DATETIME NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
<?php
$limit = 1000;
$remove = 500;
$sth = Flight::db()->prepare( 'DELETE FROM log WHERE id IN (select id from (select id FROM log ORDER BY id DESC LIMIT :limit, :remove) x)' );
$sth->bindParam( ':limit', $limit, PDO::PARAM_INT );
$sth->bindParam( ':remove', $remove, PDO::PARAM_INT );
$sth->execute();
$count = $sth->rowCount();
$sth->closeCursor();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment