How to Efficiently Create a Sequential Numbers Table in MySQL?

Create a table of consecutive numbers in MySQL

This article introduces how to efficiently generate a continuous number table in a MySQL database.

Syntax and execution issues:

The errors in the sample code are mainly due to the lack of grammatical elements such as semicolons and commas. Additionally, repeatedly selecting the maximum value from the table in a loop is inefficient.

Use generator alternative:

In order to generate a number sequence efficiently, it is recommended to use a generator instead of a loop. The following query creates a series of views that generate a sequence of numbers:

<code class="language-sql">CREATE OR REPLACE VIEW generator_16 AS SELECT 0 n UNION ALL SELECT 1 ... UNION ALL SELECT 15;
CREATE OR REPLACE VIEW generator_256, generator_4k, generator_64k, generator_1m ...</code>

(Note: During actual creation, the SQL statements of generator_256, generator_4k, generator_64k, generator_1m and other views need to be adjusted as needed to generate the corresponding number sequence)

Insert numbers into the table:

Insert numbers into the "numbers" table using the following query:

<code class="language-sql">INSERT INTO numbers(number) SELECT n FROM generator_64k WHERE n <p>此查询从“generator_64k”视图中选择行，并筛选指定范围内的数字。</p><p><strong>实现说明：</strong></p></code>

• The generated table will contain a primary key for each number.
• All views should be created before inserting data into the table.
• The maximum range of numeric columns is determined by the builder view used.

Through the above method, you can effectively avoid performance problems caused by loops and efficiently create MySQL tables containing consecutive numbers. Please adjust the scope of the generator view according to actual needs.

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