DEV Community

Cover image for MariaDB Quick-tip #1 - Range of int
Allan Simonsen
Allan Simonsen

Posted on • Edited on

MariaDB Quick-tip #1 - Range of int

#mysql #sql #mariadb

MariaDb tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Range of int

When testing your code you may need a range on integers and the trick for generating such a range is to use a local variable that you increment by one for each row.

In the code below we are using the information_schema.COLUMNS table, so be aware that this specific query will only generate a maximum of numbers that is the the same as the information_schema.COLUMNS table, but you can use any one of your table to get the same effect.
You have to do something slightly different to generate a range of int on the
SQL Server.

CREATE OR REPLACE TEMPORARY TABLE int_range (num int); SET @range_limit = 24; INSERT INTO int_range SELECT 0 UNION SELECT @rownum := @rownum + 1 FROM information_schema.COLUMNS C, (SELECT @rownum := 0) r WHERE @rownum < @range_limit; SELECT num FROM int_range
Enter fullscreen mode Exit fullscreen mode

Screen dump of DBeaver UI

Top comments (2)

Collapse
 
darkain profile image
Vincent Milum Jr •

This actually isn't needed in MariaDB, it has a built in feature called the "Sequence" engine.

For example, you can use the following query to get the exact same thing:

select * from seq_1_to_24;
Enter fullscreen mode Exit fullscreen mode

There are more complex queries supported too, such as every 3rd number rather than every number. mariadb.com/kb/en/sequence-storage...

Collapse
 
coderallan profile image
Allan Simonsen •

Thanks a lot. I did not know about the Sequence Storage Engine.