Dean Ellis

If you write enough reports in SQL, eventually you need to dynamically generate dates and such and either populate a table with a large number of sequential integers, or do something insane with UNIONs to produce the numbers. At least, I eventually always need to do this.

One of the new features in our upcoming MariaDB 10 release simplifies that for me: the SEQUENCE storage engine.

You can read about the SEQUENCE storage engine in the MariaDB Knowledge Base.

Installing it is simple enough:

INSTALL PLUGIN sequence SONAME 'ha_sequence.so';

Usage is also simple enough:

USE test;
SELECT seq FROM seq_1_to_5;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+

SELECT seq FROM seq_1_to_10_step_3;
+-----+
| seq |
+-----+
|   1 |
|   4 |
|   7 |
|  10 |
+-----+

SELECT seq FROM seq_1_to_10_step_3 WHERE seq % 2;
+-----+
| seq |
+-----+
|   1 |
|   7 |
+-----+

This has a number of uses, but a couple of examples loosely based on things I do:


SELECT NOW() - INTERVAL (seq) DAY x FROM seq_1_to_5;
+---------------------+
| x                   |
+---------------------+
| 2014-01-27 18:23:23 |
| 2014-01-26 18:23:23 |
| 2014-01-25 18:23:23 |
| 2014-01-24 18:23:23 |
| 2014-01-23 18:23:23 |
+---------------------+

SELECT LAST_DAY(NOW() - INTERVAL (seq) MONTH) x FROM seq_1_to_15;
+------------+
| x          |
+------------+
| 2013-12-31 |
| 2013-11-30 |
| 2013-10-31 |
| 2013-09-30 |
| 2013-08-31 |
| 2013-07-31 |
| 2013-06-30 |
| 2013-05-31 |
| 2013-04-30 |
| 2013-03-31 |
| 2013-02-28 |
| 2013-01-31 |
| 2012-12-31 |
| 2012-11-30 |
| 2012-10-31 |
+------------+

It behaves like any other table, so you can JOIN against it, use ORDER BY operations and so on, opening up a number of other possibilities. It simplifies a lot of things for me, and hopefully I'll never do this again:

SELECT (a+b) x FROM ( SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) zz1 CROSS JOIN ( SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) zz2;

:)

Give it a try! Or, if you want to read even more about it, check out Federico Razzoli's blog post.

Tags: 
robertospadim

Hi guy! I'm with a doubt, but i didn't tested yet...
think about a database with a myisam table name "seq_1_to_10"
how to avoid selects from wrong table? should i consider always using
"from information_schema.seq_1_to_10"
for sequencial engine tables?

berndbuffen

Hello,

there is a small error in the plugin. you cant use the engine in the
information_schema. This results in an error

USE information_schema;
SELECT seq FROM seq_1_to_5;
Unknown table 'seq_1_to_5' in information_schema

In this case, one can use it so:

SELECT seq FROM mysql.seq_1_to_5;

Newsletter Signup

Subscribe to get MariaDB tips, tricks and news updates in your inbox: