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: 

About the Author

Dean Ellis's picture

Dean Ellis is Vice President of Technical Support at SkySQL.

Dean was hired by the original MySQL AB corporation as a support engineer and developer in 2003, after some years of providing MySQL-related help to the community via the freenode IRC network. He began using MySQL in 1999 while working as a software developer and DBA, eventually deploying MySQL for web and enterprise applications alongside Oracle, Microsoft SQL Server and Lucene.

He moved into management and took over responsibility for the global support organization after MySQL AB was acquired by Sun Microsystems, retaining that role after Oracle Corporation acquired Sun. Dean left Oracle to join SkySQL in 2010, with the goal of creating a new and better home for support customers and engineers.

Dean now leads the global SkySQL Support organization from Nashville, Tennessee.

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: