How Can I Efficiently Create a Large Numbers Table in MySQL?

Create a large "table of numbers" in MySQL

You set out to generate a large table of consecutive numbers in MySQL. Your target is a table with only two columns: the primary key and a numeric column ranging from 0 to a very large number (approximately 64,000). However, your initial attempt encounters an error.

To fix this problem, you first check your code. You quickly discovered that the semicolons and commas were missing and made the necessary corrections. However, despite correct syntax, the code still does not produce the expected output.

Realizing the inefficiency of using a loop to repeatedly select the largest number from the table for each insertion, you move to a more efficient solution. You implemented a generator based on the technique described in the "Use the Index, Luke" blog post. These generators provide a more direct and efficient way to generate sequences of numbers.

With the generator you can easily generate tables of up to 64,000 numbers or more. Here is an example:

<code class="language-sql">INSERT INTO numbers(number)
SELECT n FROM generator_64k WHERE n < 64000;</code>

This statement inserts numbers into your table using the generator_64k view, which generates numbers up to 64,000 in an efficient manner.

The above is the detailed content of How Can I Efficiently Create a Large Numbers Table in MySQL?. For more information, please follow other related articles on the PHP Chinese website!