Number Sequence in MySQL
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 开发者_StackOverflow中文版in MySQL?
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.
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
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.)
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.
精彩评论