MySQL Sequence

Question

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

Answer 1

Since there is no such thing as xrange, you can use a separate table stored with an integer (as previously mentioned), or simply make a stored procedure to complete the task:

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; // 

Using:

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

The above will obviously be slower than creating a finished table with integers. He's more flexible though.

Answer 2

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

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

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

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

Use the table to generate sequence numbers as follows:

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

The UPDATE statement increments the sequence counter and calls

The next call is LAST_INSERT_ID () to return the updated value. The SELECT statement retrieves this 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 more here , as well as some discussion on it here

Answer 3

Try whole tables . This 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 the last select view with the name, say, xrange999 , then you can simply:

 SELECT iii FROM xrange999 WHERE iii BETWEEN 0 AND 9 

(Of course, you can only do this with a ten-row integers table, but I find a thousand integers more useful.)

Answer 4

If Python is your programming language, you can map the same range of Python 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 requested, but a single line of Python code that will process any range up to the maximum length of the MySQL statement.