How to delete all records created today?

Question

I am dealing with a very big database ~ 6 Million records. I've added ~30,000 bad records today. How can I delete all of the records created today in MySQL?

Solution 1

It seems created_at is a datetime. Try:

delete from table
where date(created_at) = curdate()

Of course, run a select * prior to run this query and make sure the data you're going to delete is the one you really want to delete.

Solution 2

The condition

WHERE created_at >= '2012-03-25' 
  AND created_at < '2012-03-26'

could be used to identify the rows (and quite efficiently if there is an index on created_at).

Before deleting, make sure you backup the table (or even better, the whole database). Additionally, you can use some (temp or permament) table to have the rows stored, before deleting them from your table. Then, you delete this temp table when you are sure you have erased the offending data - and nothing else:

CREATE TABLE wrong_data AS
  SELECT *
  FROM tableX
  WHERE created_at >= '2012-03-25' 
    AND created_at < '2012-03-26' ;

DELETE t
FROM tableX AS t
  JOIN wrong_data AS w
    ON w.PK = t.PK ;