How to generate a numeric sequence (data) in MySQL

Periodically when developing any project you want to generate the data in tables, then to run tests to evaluate the performance of the work (or used no indexes, how long it marks the request with a large sample, etc.). It usually takes the realized functionality of the API () function (php node.js etc.) and are run through the CLI to populate tables (insert). The disadvantage is that it is impossible to make this quick.

Especially if the data necessary to generate tens of millions of rows. In the study of PostgreSQL I found that there is already a ready function generate_series() returns a table, which then easily can be redirected to insert data into another table. Very easy and convenient to use, allowing you to specify the interval of generating values. Will give a few examples, in order to then go on to consider such implementation in a MySQL database.

Example of generation of the numeric sequence.

the

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

If you do the explain for further information.

the

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)

Example of generation of a numerical sequence, followed by the rate table.

the

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

the

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 function in PostgreSQL you can write yourself at the SQL level and, accordingly, to describe their required sequence. For example, license plates, documents, receipts. documentation presents interesting options for generating text, list, date, etc.

Back to the MySQL database. Is there any similar functionality?

Search on the Internet showed that this feature appeared in the MariaDB database (MySQL OSVETLENIE) starting with version 10. Implementation not implemented as functions, but as an additional separate database engine, similar as innodb, myisam. Method of use is also interesting and very comfortable.

Generate a numeric sequence from 1 to 5.

the

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.

the

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've probably guessed, the first number indicates the initial value, the second maximum value, the third step of the iteration. The analogue of a simple cycle through a while loop. For example, in PHP.

the

<?php

function seq($start, $stop, $step) {

$iter = 0;

while($start <= $stop) {

echo "{$iter} => {$start} \n";

$start += $step;
$iter += 1;
}

}
seq(1,15,2);
?>

the

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

The functionality is not limited only to generation. You can make associations, to work with normal tables.

the

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)

the

MariaDB [metemplate]> select example.a, example.b from example inner join (select seq from seq_1_to_15) as the generate on the 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 documentation By default, this engine is not connected and you need to execute the command.
the

INSTALL SONAME "ha_sequence";

It may be of interest even to see the table through explain, where the specified engine sequence.

the

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)

the

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)

the

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 is a kind of spike the decisions about how something solve this issue, but in fact it does not.

Example 1.

the

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

the

MariaDB [metemplate]> select * from foo where seq <= 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.

the

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)

the

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)

Article based on information from habrahabr.ru