How to Make a Row Generator in MySQL

How do I make a row generator in MySQL?

Hate to say this, but MySQL is the only RDBMS of the big four that doesn't have this feature.

In Oracle:

SELECT  *
FROM    dual
CONNECT BY
        level < n

In MS SQL (up to 100 rows):

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < n
        )
SELECT  *
FROM    hier

or using hint up to 32768

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < 32768
        )
SELECT  *
FROM    hier
OPTION (MAXRECURSION 32767) -- 32767 is the maximum value of the hint

In PostgreSQL:

SELECT  *
FROM    generate_series (1, n)

In MySQL, nothing.

generate many rows with mysql

try this one:

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

Generate an integer sequence in MySQL

There is no sequence number generator (CREATE SEQUENCE) in MySQL. Closest thing is AUTO_INCREMENT, which can help you construct the table.

MySQL Sequence Number Generate as ROW

you can use this

SELECT @n:=(@n := @n +1) AS seq_no 
FROM someTable
CROSS JOIN (SELECT @n:= 0) AS parameter;

to use you old queries you can put it there:

SELECT @n:=(@n := @n +1) AS seq_no 
FROM ( SELECT ... OLD QUERY) AS q
CROSS JOIN (SELECT @n:= 0) AS parameter;

SAMPLE

MariaDB []> select * from abc;
+----+------+
| id | b    |
+----+------+
| 33 |  100 |
| 34 |  101 |
| 35 |   11 |
| 36 |   22 |
| 37 |   22 |
| 38 |   11 |
+----+------+
6 rows in set (0.00 sec)

MariaDB []> SELECT @n:=(@n := @n +1) AS seq_no  FROM ( SELECT * from abc) AS q CROSS JOIN (SELECT @n:= 0) AS parameter;
+--------+
| seq_no |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
+--------+
6 rows in set (0.00 sec)

MariaDB []>

To generate only a number from to you can use this:

SELECT 0 AS seq_no UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8   UNION ALL SELECT 9

In MariaDB is a sequence Engine there you can do all

MariaDB []> select seq from seq_1_to_10;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
+-----+
10 rows in set (0.04 sec)

MariaDB []>

The next try - at BETWEEN you can set the boundary

SELECT *
FROM (
  SELECT (d1.seq_no*10)+d2.seq_no AS seq_no
  FROM (
    SELECT 0 AS seq_no UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8   UNION ALL SELECT 9
    ) AS d1
 CROSS JOIN (
    SELECT 0 AS seq_no UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8   UNION ALL SELECT 9 
  ) AS d2
) AS result
WHERE seq_no BETWEEN 1 AND 13
ORDER BY seq_no;

SAMPLE

MariaDB []> SELECT *
    -> FROM (
    ->   SELECT (d1.seq_no*10)+d2.seq_no AS seq_no
    ->   FROM (
    ->     SELECT 0 AS seq_no UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    ->     SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    ->     SELECT 8   UNION ALL SELECT 9
    ->     ) AS d1
    ->  CROSS JOIN (
    ->     SELECT 0 AS seq_no UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    ->     SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    ->     SELECT 8   UNION ALL SELECT 9
    ->   ) AS d2
    -> ) AS result
    -> WHERE seq_no BETWEEN 1 AND 13
    -> ORDER BY seq_no;

+--------+
| seq_no |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
|      8 |
|      9 |
|     10 |
|     11 |
|     12 |
|     13 |
+--------+
13 rows in set (0.00 sec)

MariaDB []>

Making one row many rows

Thanks for JB Nizet for telling me that MySQL would not be able to do this easily on it's own. It inspired me to write this PHP script:

$result = mysqli_query($dbc,"select abbrev,chapters from books");
while ($output = mysqli_fetch_array($result,MYSQL_ASSOC)) {
 for ($i=1; $i<=$output['chapters']; $i++) {
  echo "{$output['abbrev']}$i\n";
 }
}

How to generate 1000000 rows with random data?

Try it with a stored procedure (replace 1000 with desired amount of rows, and 2014 with test year, also see generate random timestamps in mysql)

CREATE TABLE `data` 
(
  `id`         bigint(20) NOT NULL      AUTO_INCREMENT,
  `datetime`   timestamp  NULL          DEFAULT CURRENT_TIMESTAMP,
  `channel`    int(11)                  DEFAULT NULL,
  `value`      float                    DEFAULT NULL,

  PRIMARY KEY (`id`)
);


DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000 DO
    INSERT INTO `data` (`datetime`,`value`,`channel`) VALUES (
      FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
      ROUND(RAND()*100,2),
      1
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL generate_data();

Modify to your needs. To delete the procedure:

DROP PROCEDURE generate_data;

Maybe this can give you a start!

Generating a range of numbers in MySQL

If you need the records in a table and you want to avoid concurrency issues, here's how to do it.

First you create a table in which to store your records

CREATE TABLE `incr` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Secondly create a stored procedure like this:

DELIMITER ;;
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    INSERT incr VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;
END;;
DELIMITER ;

Lastly call the SP:

CALL dowhile();
SELECT * FROM incr;

Result

Id
1
2
3
4
5

Auto generate multiple rows from one query php mysql

In mysql you cannot make new row out like that. But you can create columns.

In that case you can run example like

select 
  *, 
  Amount * 0.01 as interest, 
  Amount * 0.005 as commission 
from your_mega_table;

And if you modify your code you can get 3 versions of row with different amounts.

Otherwise you need to do some kind of cron script or trigger or stored procedure in database, that will solve your case.

How can one generate unique values for each row in a new column in a mysql table?

Your query is slow because your IS NULL condition will produce FULL SCAN each time - and that is because, obviously, you can't still use index on the column (you have not it yet)

You can use MySQL variables to generate your unique values. That will be:

UPDATE stuff CROSS JOIN (SELECT @pk:=0) AS init SET stuff.pk=@pk:=@pk+1

---

Related Topics