Generating a range of numbers in MySQL


Generating a range of numbers in MySQL



How do I generate a range of consecutive numbers (one per line) from a MySQL query so that I can insert them into a table?

For example:

nr 1 2 3 4 5 

I would like to use only MySQL for this (not PHP or other languages).




query join question

1:



Using mySql data in ASP.NET application
If you need the records in a table and you want to avoid concurrency issues, here's how to do it..
Is it possible to insert a row but only if a value does not already exist?
First you create a table in which to store your records.
Is there a site which simply outputs the latest stable version numbers of php and mysql?
CREATE TABLE `incr` (   `Id` int(11) NOT NULL auto_increment,   PRIMARY KEY  (`Id`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 
Secondly create a stored procedure like this:.
How to turn off mysql errors from being displayed to screen in CodeIgniter
DELIMITER ;; CREATE PROCEDURE dowhile() BEGIN   DECLARE v1 INT DEFAULT 5;   WHILE v1 > 0 DO     INSERT incr VALUES (NULL);     SET v1 = v1 - 1;   END WHILE; END;; DELIMITER ; 
Lastly call the SP:.
Mysql throwing query error yet finishing query just fine-why?
CALL dowhile(); SELECT * FROM incr; 
Result.
SQL: Get all posts with any comments
Id 1 2 3 4 5 


Updating an associative table in MySQL

2:


Here is one way to do it set-based without loops.

This can also be made into a view for re-use.

The example shows the generation of a sequence from 0 through 999, but of course, it may be modified to suit..
INSERT INTO     myTable     (     nr     ) SELECT     SEQ.SeqValue FROM ( SELECT     (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue FROM     (     SELECT 0  SeqValue     UNION ALL     SELECT 1 SeqValue     UNION ALL     SELECT 2 SeqValue     UNION ALL     SELECT 3 SeqValue     UNION ALL     SELECT 4 SeqValue     UNION ALL     SELECT 5 SeqValue     UNION ALL     SELECT 6 SeqValue     UNION ALL     SELECT 7 SeqValue     UNION ALL     SELECT 8 SeqValue     UNION ALL     SELECT 9 SeqValue     ) ONES CROSS JOIN     (     SELECT 0 SeqValue     UNION ALL     SELECT 10 SeqValue     UNION ALL     SELECT 20 SeqValue     UNION ALL     SELECT 30 SeqValue     UNION ALL     SELECT 40 SeqValue     UNION ALL     SELECT 50 SeqValue     UNION ALL     SELECT 60 SeqValue     UNION ALL     SELECT 70 SeqValue     UNION ALL     SELECT 80 SeqValue     UNION ALL     SELECT 90 SeqValue     ) TENS CROSS JOIN     (     SELECT 0 SeqValue     UNION ALL     SELECT 100 SeqValue     UNION ALL     SELECT 200 SeqValue     UNION ALL     SELECT 300 SeqValue     UNION ALL     SELECT 400 SeqValue     UNION ALL     SELECT 500 SeqValue     UNION ALL     SELECT 600 SeqValue     UNION ALL     SELECT 700 SeqValue     UNION ALL     SELECT 800 SeqValue     UNION ALL     SELECT 900 SeqValue     ) HUNDREDS ) SEQ 


3:


Here's a hardware engineer's version of Pittsburgh DBA's solution:.
SELECT     (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue) SeqValue FROM     (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1     CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2     CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4     CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8     CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16; 


4:


Let's say you want to insert numbers 1 through 100 into your table.

As long as you have some other table that has at least that many rows (doesn't matter the content of the table), then this is my preferred method:.
INSERT INTO pivot100  SELECT @ROW := @ROW + 1 AS ROW  FROM someOtherTable t  join (SELECT @ROW := 0) t2  LIMIT 100 ; 
Want a range that starts with something other than 1? Just change what @ROW gets set to on the join..


5:


DECLARE i INT DEFAULT 0;  WHILE i < 6 DO   /* insert into table... 

*/ SET i = i + 1; END WHILE;


6:


As you all understand, this is rather hacky so use with care.
SELECT id % 12 + 1 as one_to_twelve FROM any_large_table group by one_to_twelve 



82 out of 100 based on 72 user ratings 622 reviews

*