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

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?

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.

Filtering the first 3 ID's and delete the rest, you can do it with this query:

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

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

If your version of MySQL complains about subqueries in a delete statement, try

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

Try this:

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

I'm not sure what you were trying to do with the second large limit value, but subqueries like this generally make working in SQL much more simple.