sql-在MySQL中生成一定范围的数字
如何从MySQL查询生成一系列连续数字(每行一个),以便可以将它们插入表中?
例如:
nr
1
2
3
4
5
我只想为此使用MySQL(不使用PHP或其他语言)。
nicudotro asked 2020-07-20T09:42:04Z
9个解决方案
64 votes
这是一种基于集合的无循环方法。 也可以将其制成视图以供重用。 该示例显示了从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
Pittsburgh DBA answered 2020-07-20T09:42:57Z
48 votes
这是匹兹堡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;
David Ehrmann answered 2020-07-20T09:43:17Z
29 votes
如果您需要表中的记录,并且希望避免并发问题,请按照以下方法操作。
首先,您创建一个表来存储记录
CREATE TABLE `incr` (
`Id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
其次,创建一个存储过程,如下所示:
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
Sklivvz answered 2020-07-20T09:42:37Z
17 votes
假设您要在表格中插入数字1到100。 只要您有其他表至少具有那么多的行(与表的内容无关),那么这就是我的首选方法:
INSERT INTO pivot100
SELECT @ROW := @ROW + 1 AS ROW
FROM someOtherTable t
join (SELECT @ROW := 0) t2
LIMIT 100
;
想要一个以1以外的数字开头的范围吗? 只需更改联接上的@ROW设置即可。
JaredC answered 2020-07-20T09:43:42Z
5 votes
DECLARE i INT DEFAULT 0;
WHILE i < 6 DO
/* insert into table... */
SET i = i + 1;
END WHILE;
Tomalak answered 2020-07-20T09:43:58Z
5 votes
大家都知道,这很hacky,所以要小心使用
SELECT id % 12 + 1 as one_to_twelve FROM any_large_table group by one_to_twelve
Jakob Eriksson answered 2020-07-20T09:44:17Z
1 votes
我知道(在MySQL中)创建具有较长序列的表的“最短”方法是(交叉)将现有表与自身连接。 由于任何(通用)MySQL服务器都有0表,因此我将使用它:
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; --
通常,一个联接应该足以创建超过1M的行-但是再进行一个联接不会损害:-)-只是不要忘记设置一个限制。
如果要包括0,则应“删除” AUTO_INCEMENT属性。
ALTER TABLE seq ALTER i DROP DEFAULT;
ALTER TABLE seq MODIFY i MEDIUMINT;
现在您可以插入0
INSERT INTO seq (i) VALUES (0);
和负数
INSERT INTO seq (i) SELECT -i FROM seq WHERE i <> 0;
您可以使用
SELECT MIN(i), MAX(i), COUNT(*) FROM seq;
Paul Spiegel answered 2020-07-20T09:45:00Z
0 votes
我想分享的想法并不是对问题的精确回答,但对某些人可能有用,因此我想分享一下。
如果您经常只需要一组有限的数字,那么用您可能需要的数字创建表并每次都使用该表将是有益的。 例如:
CREATE TABLE _numbers (num int);
INSERT _numbers VALUES (0), (1), (2), (3), ...;
仅当您需要的数字低于某个合理的限制时,才可以应用此方法,因此不要将其用于生成序列1 ... 100万,而可以将其用于数字1 ... 10k。
如果在_numbers表中有此数字列表,则可以编写如下查询,以获取字符串的各个字符:
SELECT number, substr(name, num, 1)
FROM users
JOIN _numbers ON num < length(name)
WHERE user_id = 1234
ORDER BY num;
如果您需要大于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
Csongor Halmai answered 2020-07-20T09:45:38Z
0 votes
与已接受的响应非常相似,但对mysql> = 8.0使用新的WITH语法,这使内容更清晰易读,意图也更加清晰
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;
elyalvarado answered 2020-07-20T09:45:58Z