在 MySQL 中生成一系列数字

2021-11-20 00:00:00 sql 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

相关文章