Способы генерации числовой последовательности (данных) в MySQL

Периодически при разработке какого либо проекта требуется сгенерировать данные в таблицах, чтобы потом прогнать по тестам для оценки производительности работы (используются или нет индексы, как долго исполняется запрос при большой выборке и т.д.). Для этого обычно берется реализованная функциональность API (функции) (php, node.js и т.д.) проекта и прогоняются через CLI для заполнения данными таблиц (insert). Неудобство заключается в том, что нельзя это сделать по быстрому.

Особенно, если данных надо генерировать на десятки миллионы строк. При изучении БД PostgreSQL я обнаружил, что здесь уже есть готовая функция generate_series() возвращающая таблицу, которую потом легко можно перенаправлять на вставку данных в другую таблицу. Очень легка и удобна в использовании, позволяющая указать интервал генерации значений. Приведу несколько примеров, для того, чтобы потом перейти к рассмотрению подобной реализации в БД MySQL.

Пример генерации числовой последовательности.

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

Если сделать дополнительно explain для информации.

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)

Пример генерации числовой последовательности с последующей ставкой в таблицу.

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)

Подобные функции в PostgreSQL можно писать самому на уровне SQL и соответственно описывать свои необходимые последовательности. К примеру, номерные знаки автомобилей, документов, кассовых чеков. В документации представлены интересные варианты генерации текста, списка дат и т.д.

Вернемся к БД MySQL. Есть ли подобная функциональность?

Поиск по интернету показал, что данная возможность появилась в БД MariaDB (ответление MySQL) начиная c 10 версии. Реализация выполнена не ввиде функции, а как отдельный дополнительный движок базы данных, по аналогии как innodb, myisam. Способ использования также интересен и очень удобен.

Генерация числовой последовательности от 1 до 5.

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

Генерация числовой последовательности от 1 до 15, с интервалом 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)

Как Вы уже наверное догадались, первым числом указывается начальное значение, второе максимальное значение, третье — шаг итерации. Аналог простейшего цикла через while. Для примера, на 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

Функциональность не ограничивается только генерацией. Можно делать объединения, работать как с нормальными обычными таблицами.

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)

Более детальные примеры можно просмотреть в документации По умолчанию данный движок не подключен и необходимо выполнить команду.

INSTALL SONAME "ha_sequence";

Можно для интереса даже просмотреть таблицу через explain, где в качестве движка указан sequence.

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)

Что же делать с более ранними версиями MySQL (MariaDB)? В этом случае есть своего рода костыльные решения, которые приблизительно, как — то решают данный вопрос, но по сути это совсем не то.

Пример 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)

Пример 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)