How to generate X…Y sequences in Mysql

Denys Golotiuk

Follow

2 min read

·

Aug 12, 2022

Listen

Share

Numeric sequences

At some point of your DB engineeing journey you might need to generate tables from sequences of numbers:

Mysql 8.0+ allows generating result sets with numeric sequences easily:

WITH RECURSIVE seq AS (
  SELECT 1 AS v UNION ALL SELECT v + 1 FROM seq WHERE v < 30
) SELECT v FROM seq;

Here we generate numbers from 1 to 30 incremented by 1 leveraging recursive expressions.

+------+
| v    |
+------+
|    1 |
|    2 |
|    3 |
  ...

Date sequences

Not only numbers can we generate using this approach, but date sequences as well:

WITH RECURSIVE seq AS (
  SELECT '2022-01-01' AS v UNION ALL SELECT v + interval 1 day FROM seq WHERE v < '2022-01-31'
) SELECT v FROM seq;

Here we generate dates sequence from 2022-01-01 to 2022-01-31:

+------------+
| v          |
+------------+
| 2022-01-01 |
| 2022-01-02 |
| 2022-01-03 |
  ...

String values

Let’s play a bit and generate table of (pseudo-)random strings using numeric sequence and md5:

WITH RECURSIVE seq AS (
  SELECT 1 AS v UNION ALL SELECT v + 1 FROM seq WHERE v < 30
) SELECT MD5(v) FROM seq;

Here we generate dates sequence from 2022-01-01 to 2022-01-31:

+----------------------------------+
| MD5(v)                           |
+----------------------------------+
| c4ca4238a0b923820dcc509a6f75849b |
| c81e728d9d4c2f636f067f89cc14862c |
| eccbc87e4b5ce2fe28308fd9f2a7baf3 |
  ...

Quickly fill table with test data

One perfect use case for this feature is to generate any number of test rows for your tables. Let’s assume we have such table:

CREATE TABLE `tst` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` text DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Now let’s quicky generate and insert 100k test rows into tst table:

SET cte_max_recursion_depth = 1000000; 
INSERT INTO tst(val) SELECT MD5(v) FROM (
  WITH RECURSIVE seq AS ( SELECT 1 AS v UNION ALL SELECT v + 1 FROM seq WHERE v < 100000 )
  SELECT v FROM seq
) gen;

First of all we have increased value of cte_max_recursion_depth option which limits maximum recursion depth. After that we've used md5() function to generate some text value to insert into our table val column.