Mysql uses the same content of data to sort, paging data is not fixed?

problem description

in the table, id is the primary key, created_at is the timestamp, and the created_at of the data to be queried is 1538125292

.

first page:
SELECT * FROM 4s_active_slotmachine_import_player WHERE ( area_id = 18696) AND ( active_id = 6293) AND ( is_del = 0) ORDER BY created_at DESC LIMIT

Page 2:
SELECT * FROM 4s_active_slotmachine_import_player WHERE ( area_id = 18696) AND ( active_id = 6293) AND ( is_del = 0) ORDER BY created_at DESC LIMIT 20 OFFSET 20

the environmental background of the problems and what methods you have tried

it is found that the second page shows the data that appeared in the first page, and it is no problem if the query does not add order by, or it can be sorted with id.
sorting with created_at of the same value will result in the same data appearing on multiple pages.

what is this?

Jul.22,2021

testing, order by will not lead to duplicate limit paging data. Visual inspection should be a sql statement problem

first page


you can see that order by has the same data and does not affect paging


because the results of the two sorts are different, order by has the same value, so there is no guarantee that the results of the two sorts are the same. In this case, it is recommended to use multi-conditional sorting. If the first value is the same, the second value is compared.


MyISAM will read the data randomly according to the rowid in the index and replace it with InnoDB

.
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e54b4e-4c9dc.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e54b4e-4c9dc.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?