MySQL Bulk Update and Write Database

As found in the comments, we are talking about mysql 5.5 . No useful generate_series or equivalents are able, so it is necessary to pervert.

The options are not very correct

Six-digit numbers, which means a maximum of 1 million records - this is actually not too dofiga. You can subtract everything from the table by where number between :start and :end into a simple array number => true , it will take some megabytes of memory in the worst case. Then in for with the help of isset to check whether such a number is necessary in the list, if not - then accumulate in a separate array and each, that way, 1000 elements to be dumped in one query to the base. Not too correct thought, but possible.

You can do exactly the same, but not read everything, but by breaking the initial range into several blocks, for example, 50 thousand numbers each - for a full crawl, 20 requests to the database for reading will be required.

Or all the same way, but another option that allows you to control and memory consumption in the worst case and the minimum number of requests at best: read into the array where number >= :start order by number limit 10000 . If you returned less than the limit records - you act according to the first scenario, you have read all the numbers used, all the rest from start to end you need to add to the database. If exactly the number of records that is specified in limit is returned, then perhaps you have more data in the table. Then you memorize the last number from the query result, and when your for reached this number, you also make a query to the database by substituting your last number where number >= :last_number order by number limit 10000 and re-create your array with the existing id in the database. This will work well if usually there are few records and gaps in your spreadsheet often.

Again options

Stranger, but more productive. If you need to perform such a task more or less often, then create a table, for example:

 create table int_series (number int(11) unsigned not null primary key); 

In any possible way, write down the numbers from 1 to 999999 in it successively. And don't touch it anymore, it is not made to touch it, except for how to read it. Now your whole task is to call the request:

 insert into `tablename` (number) select number from int_series left join tablename using(number) where tablename.number is null and number between :start and :end 

It's all. Seriously.

For Postgresql, which was mentioned in the comments, this extra table is simply not needed, you can immediately do:

 insert into `tablename` (number) select number from generate_series(:start, :end) number left join tablename using(number) where tablename.number is null 

Of course, in all variants on the tablename.number should be built index.

A variant with sequence generation on the fly by the power of a huge join - unfortunately, it works adequately only on small ranges. The range indicated in the question was calculated in half a second - this is only a range.

If there is a large table next to it (absolutely any, as long as it contains enough rows), then you can generate sequences using a user variable:

 SELECT @i := @i + 1 AS number FROM any_big_table, (select @i:=$start) AS z limit $end - $start 

It will run more fun, but need some sort of sign. For a finite range, rather small besides, only 1 million rows - it is easier to create a table with numbers in advance.

Behind the scenes

Behind the scenes was, it seems, only an option with a stored procedure. You can write to the store, which will do exactly the naive for loop, but because of the proximity to the data, it is more efficient to do this than from the application. But one specialized query will be more efficient, and the stored logic in mysql is not the most trouble-free thing. Quite unpleasant, for example, is the silent automatic commit of a transaction when calling a stored object.