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).
In SQL, BETWEEN is a logical operator used to select a range of values from the table of the database. Using the between the query, we can also check whether a value is in the provided range or not. BETWEEN in MySQL is generally used with the SELECT statements, and with INSERT, DELETE, and UPDATE queries.
The BETWEEN Operator in MySQL is used to get the values within a specified range. The BETWEEN operator is used to compare the value in a column. This operator tests a range of values with a column value.
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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With