Generating number sequence in MySQL

Sometimes you need to create a dropdown box with a list sequential numbers.  Now that can be just plain 1,2,3.  But it could just as well be 400,500,600, ...
Use-cases are for example, you want to provide a dropdown box to resize a volume, but you don't want the operator to go below the current size and you don't want him to over 10TB for example and you always want it to be a multiple of 100GB.  How do you provide such a list ?



Well it starts with adding a couple of views in the WFA MySQL Server.  Here is MySQL code to create them
https://github.com/wfaguy/commands/tree/master/Setup%20MySQL%20Number%20Generator%20Views

CREATE OR REPLACE VIEW playground.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;
 
CREATE OR REPLACE VIEW playground.generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM playground.generator_16 lo, playground.generator_16 hi;
 
CREATE OR REPLACE VIEW playground.generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM playground.generator_256 lo, playground.generator_16 hi;
 
CREATE OR REPLACE VIEW playground.generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM playground.generator_256 lo, playground.generator_256 hi;
 
CREATE OR REPLACE VIEW playground.generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM playground.generator_64k lo, playground.generator_16 hi;

Don't know how to do this ?  Search our site for MySQL Client.

Now, once you have the view in place.  you should be able to use it right away.
Note that there are 5 generators.  One to generate a sequence of 16, 256, 4096,65536 and one for +1milion.
Sample query :

SELECT 
  n
FROM
  playground.generator_256

This will give you the number 0 to 255.  Now you can start playing with it and joining it with other elements.  For example 100 -> 10000.

SELECT 
  n*100
FROM
  playground.generator_256
WHERE 
  n>0 AND n<=100 

Or the resize of a lun, where the size cannot be lower of the current size and with a top limit.

SELECT n*512 as 'Size(GB)'
FROM 
  playground.generator_256,
  cm_storage.lun
WHERE 
  n<20 
  AND (n*512)>ROUND(lun.size_mb/1024)
  AND lun.name = '${Lun}'