Number Sequence in MySQL

Question

In Python if I wanted a sequence from 0 - 9 (inclusive) I would use xrange(0,10) . Is there a way I can do this in MySQL?

Answer 1

You can use LAST_INSERT_ID() to simulate sequences in MySQL.

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

Create a table to hold the sequence counter and initialize it:

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);

Use the table to generate sequence numbers like this:

  UPDATE sequence SET id=LAST_INSERT_ID(id+1);
  SELECT LAST_INSERT_ID();

The UPDATE statement increments the sequence counter and causes the

next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 21.9.3.37, “mysql_insert_id()”.

Read up more here as well as some discussion on it here

Answer 2

Try an integers table variant. This one is taken from Xaprb:

create table integers(i int unsigned not null);
insert into integers(i) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select (hundreds.i * 100) + (tens.i * 10) + units.i as iii
from integers as units
    cross join integers as tens
    cross join integers as hundreds;

If you made that last select a view named, say, xrange999, then you can simply:

SELECT iii FROM xrange999 WHERE iii BETWEEN 0 AND 9

(of course, you can do that with just the ten-row integers table, but I find a thousand integers a little more useful.)

Answer 3

If Python is your programming language, you can map that same Python range to create an INSERT statement of the form:

INSERT INTO Table (IDCol) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)

Not exactly what you asked for, but a single line of Python code that will handle any range up to the maximum length of a MySQL statement.

Answer 4

Since there is no such thing as xrange, one could use a separate table stored with integer (as previously answered), or just make a stored procedure to do the job:

DROP PROCEDURE IF EXISTS xrange;
DELIMITER //
CREATE PROCEDURE xrange(x INT, y INT)
BEGIN
  DECLARE i INT DEFAULT x;
  CREATE TEMPORARY TABLE xrange_tmp (c1 INT);
  WHILE i < y DO
    INSERT INTO xrange_tmp VALUES (i);
    SET i = i + 1;
  END WHILE;
END;
//

Usage:

CALL xrange(-2,10);
SELECT c1 FROM xrange_tmp;
DROP TABLE xrange_tmp;

The above is obviously going to be slower than creating a ready table with integers. It's more flexible though.