PHP - MySQL - 删除除最近3行之外的所有行＆此版本的MySQL尚不支持'LIMIT＆IN / ALL / ANY / SOME子查询'

Question

I have:

mysql_query("DELETE FROM mytable ORDER BY id DESC LIMIT 3, 999999999;") 
or die(mysql_error());

But it doesn't seem to like it. I want to keep the most recent three rows and delete everything else. Thoughts?

Followup - thanks for the simultaneous jinx!

I tried the code and got this version error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I checked my hosting and I have: MySQL version 5.5.36-cll-lve

I didn't expect this. What version do I need for limits? Is there another way to write this that will work?

Answer 1

You'd think your first attempt would actually work. However, the LIMIT keyword in the MySql DELETE command only supports a maximum number of rows, not a range of values to delete. This is why you see the error you're getting. Next, you'd think (as I thought) something like this would work:

DELETE FROM mytable WHERE id NOT IN
   (SELECT id FROM mytable ORDER BY id DESC LIMIT 3);

However, MySql doesn't seem to support the IN or NOT IN operators on a DELETE statement either. Next, you'd think something like this would work:

DELETE FROM mytable WHERE id <
   (SELECT id FROM mytable ORDER BY id DESC LIMIT 2,1)

However, it seems MySql doesn't support this idea either. There are some other posts that say you need to build a temp table, which seems insanely silly! However, with a bunch of nested selects and alias hackery, I was able to get this working:

DELETE FROM mytable WHERE id <
  (select id FROM (SELECT * FROM myTable) as t ORDER BY id DESC LIMIT 2,1);

Working Fiddle

It's unbelievable MySql makes it this difficult! Maybe it's time for a better database, such as PostgreSQL? It will just work like you'd expect.