Ways to generate a numeric sequence (data) in MySQL

Periodically, when developing a project, it is required to generate data in the tables in order to then run through the tests to assess the performance of the work (whether or not indices are used, how long the query is executed for a large sample, etc.). To do this, the implemented API functionality (functions) (php, node.js, etc.) of the project is usually taken and run through the CLI to populate the tables (insert). The inconvenience is that you can not do this quickly.



Especially if you need to generate data for tens of millions of rows. While studying the PostgreSQL database, I discovered that there is already a ready-made function generate_series () that returns a table, which can then be easily redirected to insert data into another table. Very easy and convenient to use, allowing you to specify the interval for generating values. I will cite a few examples in order to go on to consider a similar implementation in the MySQL database.



An example of generating a numeric sequence.

postgres=# SELECT * FROM generate_series(1,10); generate_series ----------------- 1 2 3 4 5 6 7 8 9 10 (10 rows) 

If you make an additional explain for information.

')

 postgres=# explain SELECT * FROM generate_series(1,10); QUERY PLAN ------------------------------------------------------------------------ Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=4) (1 row) 

An example of the generation of a numerical sequence with the subsequent rate in the table.

 postgres=# create table test (number int); CREATE TABLE postgres=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | test | table | postgres (1 row) 

 postgres=# insert into test select * from generate_series(1,10); INSERT 0 10 postgres=# select * from test; number -------- 1 2 3 4 5 6 7 8 9 10 (10 rows) 

Similar functions in PostgreSQL can be written by yourself at the SQL level and, accordingly, describe your necessary sequences. For example, license plates of cars, documents, cash receipts. The documentation presents interesting options for generating text, a list of dates, etc.



Let's return to the MySQL database. Is there any similar functionality?



An Internet search showed that this feature appeared in the MariaDB database (MySQL response) starting from version 10. The implementation is not implemented as a function, but as a separate additional database engine, by analogy as innodb, myisam. The method of use is also interesting and very convenient.



Generate a numeric sequence from 1 to 5.

 MariaDB [metemplate]> SELECT * FROM seq_1_to_5; +-----+ | seq | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | +-----+ 5 rows in set (0.00 sec) 

Generate a numeric sequence from 1 to 15, with an interval of 2.

 MariaDB [metemplate]> SELECT * FROM seq_1_to_15_step_2; +-----+ | seq | +-----+ | 1 | | 3 | | 5 | | 7 | | 9 | | 11 | | 13 | | 15 | +-----+ 8 rows in set (0.00 sec) 

As you probably already guessed, the first number indicates the initial value, the second maximum value, the third - the iteration step. Analogue of the simplest loop through while. For example, in PHP.

 <?php function seq($start, $stop, $step) { $iter = 0; while($start <= $stop) { echo "{$iter} => {$start} \n"; $start += $step; $iter += 1; } } seq(1,15,2); ?> 

 [root@localhost ~]# php while.php 0 => 1 1 => 3 2 => 5 3 => 7 4 => 9 5 => 11 6 => 13 7 => 15 

Functionality is not limited to generation. You can make joins, work as with normal regular tables.

 MariaDB [metemplate]> desc example; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | int(11) | YES | MUL | NULL | | | b | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) 

 MariaDB [metemplate]> select example.a, example.b from example inner join (select seq from seq_1_to_15) as generate on generate.seq = example.a; +------+------+ | a | b | +------+------+ | 1 | 2 | | 4 | 1 | | 2 | 7 | | 9 | 9 | | 1 | 19 | | 11 | 12 | +------+------+ 6 rows in set (0.00 sec) 

More detailed examples can be viewed in the documentation. By default, this engine is not connected and the command must be executed.

 INSTALL SONAME "ha_sequence"; 

You can even see the table for interest through explain, where sequence is specified as the engine.

 MariaDB [metemplate]> show create table seq_1_to_15\G; *************************** 1. row *************************** Table: seq_1_to_15 Create Table: CREATE TABLE `seq_1_to_15` ( `seq` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`) ) ENGINE=SEQUENCE DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 

 MariaDB [metemplate]> show index from seq_1_to_15\G; *************************** 1. row *************************** Table: seq_1_to_15 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: seq Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: Comment: Index_comment: 1 row in set (0.01 sec) 

 MariaDB [metemplate]> desc seq_1_to_15; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | seq | bigint(20) unsigned | NO | PRI | NULL | | +-------+---------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) 

What to do with earlier versions of MySQL (MariaDB)? In this case there are some kind of crutch solutions, which approximately, somehow solve this issue, but in fact this is not at all the case.



Example 1

 MariaDB [metemplate]> create table two select null foo union all select null; MariaDB [metemplate]> create temporary table seq ( foo int primary key auto_increment ) auto_increment=1 select a.foo from two a, two b, two c, two d; Query OK, 16 rows affected (0.08 sec) Records: 16 Duplicates: 0 Warnings: 0 

 MariaDB [metemplate]> select * from seq where foo <= 23; +-----+ | foo | +-----+ | 9 | | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | | 17 | | 18 | | 19 | | 20 | | 21 | | 22 | | 23 | +-----+ 15 rows in set (0.00 sec) 

Example 2

 MariaDB [metemplate]> CREATE OR REPLACE VIEW generator_16 -> AS SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL -> SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL -> SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL -> SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL -> SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL -> SELECT 15; Query OK, 0 rows affected (0.09 sec) 

 MariaDB [metemplate]> select * from generator_16; +----+ | n | +----+ | 0 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | +----+ 16 rows in set (0.01 sec)