Способы генерации числовой последовательности (данных) в 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)
    
    
    
    Поделиться публикацией
    Похожие публикации
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 10
    • 0
      А почему в заголовке MySQL, когда способа именно для MySQL и не показано?
      www.sql.ru/forum/684431/faq-numeraciya-strok-i-drugie-voprosy-pro-ispolzovanie-peremennyh
      • 0
        А для mysql нет варианта кроме как сгенерировать запрос вида select 1 union select 2 union select 3…
        По приведенной вами ссылке — про другое.
        • 0
          Как это про другое, когда именно про это?
          Запрос SELECT @i := @i + 1 AS row_number FROM your_table, (select @i:=0) AS z выводит записи с числами, начиная с единицы. Чтобы ограничить количество записсей используется LIMIT.
          Таким образом, SELECT @i := @i + 1 AS row_number FROM your_table, (select @i:=0) AS z LIMIT 16 выводит записи с числами от 1 до 16 включительно.
          Таблица your_table — любая уже имеющаяся таблица достаточного размера. Если достаточно большой таблицы нет, то можно ее перемножить саму на себя несколько раз.
          • 0
            Таким же образом последовательности дат можно генерировать, что полезно для отчётов.
            • 0
              Как именно про это, если типичный кейс для generate_series — это как раз генерация данных для пустой таблицы?

              «любая уже имеющаяся таблица», которая на самом деле-то и не нужна — это не решение, это костыль для разовых операций.
              • 0
                "«любая уже имеющаяся таблица», которая на самом деле-то и не нужна"
                Она не «не нужна», она используется для каких-то своих целей и уже есть в БД. Если нет готовой своей, что практически невероятно, то можно использовать одну из системных таблиц, хотя это может быть медленнее.
                • 0
                  А зачем такие сложности, если можно сгенерировать «виртуальную» таблицу в виде (select 1 union all select 2...) as t, умножить саму на себя и так далее?
                  • 0
                    Ваш вариант более переносим, но чисто по человечески он сложнее.
        • +1

          Часто удобнее подменить результат выполнения запроса на стороне сервера (mock, faker).

          • 0
            А как же http://dev.mysql.com/doc/refman/5.7/en/load-data.html?

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.