How can I find the deleted id between an continuous id range in MYSQL?

Question

I have some data whose id range from 1001 to 1500 for example.

but the total count of data item is 400 less than 500, some id was deleted.

can I get these deleted id using SQL?

id        name 

1001      name_1001
...

1500      name_1500

# select count(*) from table  400

thanks~

Possible duplicate of [How to find gaps in sequential numbering in mysql?](https://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql) — Maksym Fedorov, Mar 28 '19 at 09:05

score 0 · Accepted Answer · answered Mar 28 '19 at 09:12

you can these commands to restore your mySQL data >

To extract the statements from an entire binary log file, run this command:

mysqlbinlog mysql_bin.000001 | mysql -u root -ppassword database_name

if you want to extract the lost data from several binary log files, use this command:

mysqlbinlog mysql_bin.000001 mysql_bin.000002 | mysql -u root -ppassword database_name

If you know when this data was added to the database, you can extract statements by using the relevant timestamps:

mysqlbinlog --start-datetime="2017-04-20 10:01:00" \ --stop-datetime="2017-04-20 9:59:59" mysql_bin.000001 \ | mysql -u root -ppassword database_name

1 Answers1