在 MySQL 中生成一系列数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/186756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generating a range of numbers in MySQL
提问by nicudotro
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?
如何从 MySQL 查询生成一系列连续数字（每行一个），以便将它们插入表中？
For example:
例如：
nr
1
2
3
4
5
I would like to use only MySQL for this (not PHP or other languages).
我只想为此使用 MySQL（而不是 PHP 或其他语言）。
采纳答案by Sklivvz
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:
最后调用SP：
CALL dowhile();
SELECT * FROM incr;
Result
结果
Id
1
2
3
4
5
回答by Pittsburgh DBA
Here is one way to do it set-based without loops. This can also be made into a view for re-use. The example shows the generation of a sequence from 0 through 999, but of course, it may be modified to suit.
这是一种在没有循环的情况下基于集合执行此操作的方法。这也可以制成视图以供重复使用。该示例显示了从 0 到 999 的序列的生成，但当然，可以对其进行修改以适应。
INSERT INTO
myTable
(
nr
)
SELECT
SEQ.SeqValue
FROM
(
SELECT
(HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
(
SELECT 0 SeqValue
UNION ALL
SELECT 1 SeqValue
UNION ALL
SELECT 2 SeqValue
UNION ALL
SELECT 3 SeqValue
UNION ALL
SELECT 4 SeqValue
UNION ALL
SELECT 5 SeqValue
UNION ALL
SELECT 6 SeqValue
UNION ALL
SELECT 7 SeqValue
UNION ALL
SELECT 8 SeqValue
UNION ALL
SELECT 9 SeqValue
) ONES
CROSS JOIN
(
SELECT 0 SeqValue
UNION ALL
SELECT 10 SeqValue
UNION ALL
SELECT 20 SeqValue
UNION ALL
SELECT 30 SeqValue
UNION ALL
SELECT 40 SeqValue
UNION ALL
SELECT 50 SeqValue
UNION ALL
SELECT 60 SeqValue
UNION ALL
SELECT 70 SeqValue
UNION ALL
SELECT 80 SeqValue
UNION ALL
SELECT 90 SeqValue
) TENS
CROSS JOIN
(
SELECT 0 SeqValue
UNION ALL
SELECT 100 SeqValue
UNION ALL
SELECT 200 SeqValue
UNION ALL
SELECT 300 SeqValue
UNION ALL
SELECT 400 SeqValue
UNION ALL
SELECT 500 SeqValue
UNION ALL
SELECT 600 SeqValue
UNION ALL
SELECT 700 SeqValue
UNION ALL
SELECT 800 SeqValue
UNION ALL
SELECT 900 SeqValue
) HUNDREDS
) SEQ
回答by David Ehrmann
Here's a hardware engineer's version of Pittsburgh DBA's solution:
这是匹兹堡 DBA 解决方案的硬件工程师版本：
SELECT
(TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue) SeqValue
FROM
(SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16;
回答by JaredC
Let's say you want to insert numbers 1 through 100 into your table. As long as you have some other table that has at least that many rows (doesn't matter the content of the table), then this is my preferred method:
假设您想在表中插入数字 1 到 100。只要您有其他一些至少有那么多行的表（与表的内容无关），那么这是我的首选方法：
INSERT INTO pivot100
SELECT @ROW := @ROW + 1 AS ROW
FROM someOtherTable t
join (SELECT @ROW := 0) t2
LIMIT 100
;
Want a range that starts with something other than 1? Just change what @ROW gets set to on the join.
想要一个以 1 以外的数字开头的范围？只需更改 @ROW 在加入时设置的内容即可。
回答by Jakob Eriksson
As you all understand, this is rather hacky so use with care
众所周知，这是相当hacky所以要小心使用
SELECT id % 12 + 1 as one_to_twelve FROM any_large_table group by one_to_twelve
回答by Tomalak
DECLARE i INT DEFAULT 0;
WHILE i < 6 DO
/* insert into table... */
SET i = i + 1;
END WHILE;
回答by elyalvarado
Very similar to the accepted response, but using the new WITHsyntax for mysql >= 8.0 which makes a lot more legible and the intent is also clearer
与接受的响应非常相似，但使用WITHmysql >= 8.0的新语法，这使得更清晰，意图也更清晰
WITH DIGITS (N) AS (
SELECT 0 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)
SELECT
UNITS.N + TENS.N*10 + HUNDREDS.N*100 + THOUSANDS.N*1000
FROM
DIGITS AS UNITS, DIGITS AS TENS, DIGITS AS HUNDREDS, DIGITS AS THOUSANDS;
回答by Paul Spiegel
The "shortest" way i know (in MySQL) to create a table with a long sequence is to (cross) join an existing table with itself. Since any (common) MySQL server has the information_schema.COLUMNStable i would use it:
我知道（在 MySQL 中）创建具有长序列的表的“最短”方式是（交叉）将现有表与其自身连接。由于任何（常见）MySQL 服务器都有该information_schema.COLUMNS表，我会使用它：
DROP TABLE IF EXISTS seq;
CREATE TABLE seq (i MEDIUMINT AUTO_INCREMENT PRIMARY KEY)
SELECT NULL AS i
FROM information_schema.COLUMNS t1
JOIN information_schema.COLUMNS t2
JOIN information_schema.COLUMNS t3
LIMIT 100000; -- <- set your limit here
Usually one join should be enough to create over 1M rows - But one more join will not hurt :-) - Just don't forget to set a limit.
通常一个连接应该足以创建超过 100 万行 - 但多一个连接不会有什么坏处 :-) - 只是不要忘记设置一个限制。
If you want to include 0, you should "remove" the AUTO_INCEMENTproperty.
如果您想包含0，您应该“删除”该AUTO_INCEMENT属性。
ALTER TABLE seq ALTER i DROP DEFAULT;
ALTER TABLE seq MODIFY i MEDIUMINT;
Now you can insert 0
现在你可以插入 0
INSERT INTO seq (i) VALUES (0);
and negative numbers as well
以及负数
INSERT INTO seq (i) SELECT -i FROM seq WHERE i <> 0;
You can validate the numbers with
您可以使用以下方法验证数字
SELECT MIN(i), MAX(i), COUNT(*) FROM seq;
回答by Csongor Halmai
The idea I want to share is not a precise response for the question but can be useful for some so I would like to share it.
我想分享的想法不是对问题的准确回答，但对某些人可能有用，所以我想分享它。
If you frequently need only a limited set of numbers then it can be beneficial to create a table with the numbers you may need and just use that table every time. For example:
如果您经常只需要一组有限的数字，那么创建一个包含您可能需要的数字的表格并且每次只使用该表格会很有帮助。例如：
CREATE TABLE _numbers (num int);
INSERT _numbers VALUES (0), (1), (2), (3), ...;
This can be applied only if you need numbers below a certain reasonable limit, so don't use it for generating sequence 1...1 million but can be used for numbers 1...10k, for example.
仅当您需要低于某个合理限制的数字时才可以应用此方法，因此不要将其用于生成序列 1...1 百万，但可以用于数字 1...10k，例如。
If you have this list of numbers in the _numberstable then you can write queries like this, for obtaining the individual characters of a string:
如果您在_numbers表中有这个数字列表，那么您可以编写这样的查询，以获取字符串的各个字符：
SELECT number, substr(name, num, 1)
FROM users
JOIN _numbers ON num < length(name)
WHERE user_id = 1234
ORDER BY num;
If you need larger numbers than 10k then you can join the table to itself:
如果您需要大于 10k 的数字，则可以将表加入到自身中：
SELECT n1.num * 10000 + n2.num
FROM _numbers n1
JOIN _numbers n2
WHERE n1 < 100
ORDER BY n1.num * 10000 + n2.num; -- or just ORDER BY 1 meaning the first column
回答by dannymac
This is based on a previous answer (https://stackoverflow.com/a/53125278/2009581), but is compatible with MySQL 5.7. It works for replicas and read-only users:
这是基于之前的答案 ( https://stackoverflow.com/a/53125278/2009581)，但与 MySQL 5.7 兼容。它适用于副本和只读用户：
SELECT x1.N + x10.N*10 + x100.N*100 + x1000.N*1000
FROM (SELECT 0 AS N 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) x1,
(SELECT 0 AS N 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) x10,
(SELECT 0 AS N 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) x100,
(SELECT 0 AS N 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) x1000
WHERE x1.N + x10.N*10 + x100.N*100 + x1000.N*1000 <= @max;
It generates integers in the range of [0, @max].
它生成 [0, @max]范围内的整数。
