設置一個rank為之間戳,通過選擇上移,就是將本記錄與上一條記錄rank值交換,下移就是將本條記錄與下一條記錄rank值交換,置頂就是將本記錄與rank值最小的記錄交換
SELECT * FROM user ORDER BY rank ASC
+----+------+---------+ | ID | rank | name | +----+------+---------+ | 1 | 1 | admin1 | | 2 | 2 | admin2 | | 3 | 3 | admin3 | | 4 | 4 | admin4 | | 5 | 5 | admin5 | | 6 | 6 | admin6 | | 7 | 7 | admin7 | | 8 | 8 | admin8 | | 9 | 9 | admin9 | | 10 | 10 | admin10 | | 11 | 11 | admin11 | | 12 | 12 | admin12 | +----+------+---------+
select * from user where id <3 or id=(SELECT MIN(id) from user) order by id desc limit 1
+----+------+--------+ | ID | rank | name | +----+------+--------+ | 2 | 2 | admin2 | +----+------+--------+ 1 row in set (0.00 sec)
mysql> select * from user where id <1 or id=(SELECT MIN(id) from user) order by id desc limit 1; +----+------+--------+ | ID | rank | name | +----+------+--------+ | 1 | 1 | admin1 | +----+------+--------+ 1 row in set (0.00 sec)
mysql> select * from user where id > 3 OR ID=(SELECT MAX(ID) FROM user) ORDER BY id ASC LIMIT 1; +----+------+--------+ | ID | rank | name | +----+------+--------+ | 4 | 4 | admin4 | +----+------+--------+ 1 row in set (0.00 sec)
mysql> select * from user where id > 12 OR ID=(SELECT MAX(ID) FROM user) ORDER BY id ASC LIMIT 1; +----+------+---------+ | ID | rank | name | +----+------+---------+ | 12 | 12 | admin12 | +----+------+---------+ 1 row in set (0.00 sec)