在MySQL中生成一系列数字 [英] Generating a range of numbers in MySQL

问题描述

如何从MySQL查询生成一系列连续数字(每行一个)，以便可以将它们插入表中?

How do I generate a range of consecutive numbers (one per line) from a MySQL query so that I can insert them into a table?

例如:

nr
1
2
3
4
5

为此，我只想使用MySQL(而不是PHP或其他语言).

I would like to use only MySQL for this (not PHP or other languages).

推荐答案

如果您需要表中的记录，并且希望避免并发问题，请按以下步骤操作.

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 ;

最后调用SP:

CALL dowhile();
SELECT * FROM incr;

结果

Id
1
2
3
4
5

这篇关于在MySQL中生成一系列数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！