I want to delete data which are past 2years. filed name is Date and type is varchar(255)

delete from <table_name> where <Filed> like ' 22';

running very longtime but no deletion of data

CodePudding user response：

I have check and tried the query, you can try with

DELETE From <datatable> WHERE <date> LIKE ' 22';
DELETE From post WHERE date LIKE ' 22'; #Example

May you provide the database or screenshot? I have tried the query and no issue https://www.db-fiddle.com/f/syhtgVyEcSPcHRXBXHLtor/0

CodePudding user response：

If primary key(probably id) and the date column are correlated, meaning bigger id will result the later dates(in this case, it is a of type varchar, and thanks to P.Salmon for pointing this out),then

I think you can delete using primary key(normally it is column id), for example:

select id from table where date > '2020' order by id asc limit 1; 
// assume this id = 123456789, and delete rows that created before this id was created

delete from table where id < 123456789;

if there is not correlation, I have some ideas like below:

1. create a new column called created_at of type year/date/datetime/timestamp(probably date or year will do), it will store the actual year or date or datetime, use it to replace the date column of type varchar, probably create an index on created_at, and delete with the new column
2. If there is a index on date(varchar), since the % sign in like clause will cause the server not using index, so it is a full table scan for sure, and can you like enumerate all date like '01-01-2020', '01-02-2020', and delete rows one date by one date, with a script, I think in this way at least you get to use the index
3. if there are too many rows, like 10 years or even more, is it possible just migrate data within 2 years to a new table, and just remove the old table?
4. write a script, fetch 10000 row each time from beginning of primary key, and delete those that are over 2 years, and fetch next 10000

last_id = 0

select * from table where id > last_id order by id asc limit 10000;
last_id = [last id of the query]
delete from table where id in (xxx);