generate many rows with mysql

Question

Hi:

In postgres there is a built in function generate_series() which can generate rows with number.

is there a function in mysql does same thing?

Answer 1

AUTO_INCREMENT

Or, maybe you mean How do I make a row generator in mysql.

Answer 2

try this one:

select @rownum:=@rownum+1 n, t.* from tbl t, (SELECT @rownum:=0) r order by somefield

Answer 3

If all else fails, you could replicate the function in MySQL as a procedure.
Something like this might work

DELIMITER //
DROP PROCEDURE IF EXISTS `generate_series`//
CREATE PROCEDURE `generete_series`(p_start Int, p_end Int)
BEGIN
    /* We need a temporary table to hold the values until we can output them */
    CREATE TEMPORARY TABLE `temp_series`( val Int Not Null );   

    /* Add all the values in the range to the temp table. */
    set @insert_query = CONCAT('INSERT INTO `temp_series` VALUES (', p_start, ')');
    set @ind = p_start + 1;
    while @ind <= p_end do
        set @insert_query = CONCAT(@insert_query, ',(', @ind, ')');
        set @ind = @ind + 1;
    end while;

    prepare stmt FROM @insert_query;
    execute stmt;

    /* Select the values and echo them back. */
    SELECT * FROM `temp_series`;

    /* Free resources. This isnt needed, technically, unless
     * you plan on using the function multiple times per connection */
    DROP TABLE `temp_series`;
END//
DELIMITER ;

Note that this is not really a very efficient procedure, as it uses a temporary table and prepared query. Not a good thing to use very frequently.

You should look for alternative methods. There is most likely a better way to do whatever it is you are trying to do, without having to resort to this.