Время от времени при разработке проекта необходимо сформировать данные в таблицах и затем прогнать их через тесты, чтобы оценить производительность работы (используются или нет индексы, сколько времени занимает выполнение запроса с большой выборкой и т.д. ).
Для этого обычно берут реализованный API функционал (функции) (php, node.js и т.д.) проекта и запускают его через CLI для заполнения таблиц данными (вставками).
Неудобство в том, что сделать это быстро невозможно.
Особенно если данные нужно сгенерировать для десятков миллионов строк.
При изучении базы данных PostgreSQL я обнаружил, что здесь уже есть готовая функция генерировать_серию() возврат таблицы, которую затем можно легко перенаправить для вставки данных в другую таблицу.
Очень простой и удобный в использовании, позволяющий указать интервал формирования значений.
Приведу несколько примеров, чтобы затем перейти к рассмотрению аналогичной реализации в базе данных MySQL. Пример генерации числовой последовательности.
Если делать дополнительные пояснения к информации.postgres=# SELECT * FROM generate_series(1,10); generate_series ----------------- 1 2 3 4 5 6 7 8 9 10 (10 rows)
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) начиная с 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";
Ради интереса вы даже можете просмотреть таблицу с помощью объяснения, где в качестве движка указана последовательность.
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)
Теги: #MySQL #postgresql #MySQL #postgresql
-
Переводчик С Delphi На Javascript
19 Oct, 24
