慕容森
2019-09-03 19:32:05
我正在嘗試在MySQL中生成一個(gè)連續(xù)數(shù)字的大表。我只想要2列; 主鍵和數(shù)字列,范圍為0-X,其中X非常大。約。64,000行應(yīng)該這樣做。我試過(guò)這段代碼沒(méi)有成功:CREATE TABLE numbers ( number INT NOT NULL CONSTRAINT XPKnumbers PRIMARY KEY CLUSTERED (number) )INSERT INTO numbers (number) VALUES (0)DECLARE @i INTSET @i = 20WHILE 0 < @i BEGIN INSERT INTO numbers (number) SELECT number + (SELECT 1 + Max(number) FROM numbers) FROM numbers SET @i = @i - 1 ENDSELECT * FROM numbers我收到此錯(cuò)誤:#1064 - 您的SQL語(yǔ)法有錯(cuò)誤; 查看與您的MySQL服務(wù)器版本對(duì)應(yīng)的手冊(cè),以便在'CONSTRAINT XPKnumbers PRIMARY KEY CLUSTERED(數(shù)字)附近使用正確的語(yǔ)法。在第3行插入n'有人建議讓這項(xiàng)工作嗎?
3 回答

白板的微信
TA貢獻(xiàn)1883條經(jīng)驗(yàn) 獲得超3個(gè)贊
您在列和約束聲明之間缺少逗號(hào):
CREATE TABLE numbers (
number INT NOT NULL,
CONSTRAINT XPKnumbers
PRIMARY KEY CLUSTERED (number)
)

慕桂英4014372
TA貢獻(xiàn)1871條經(jīng)驗(yàn) 獲得超13個(gè)贊
從MySQL 8.0開(kāi)始,您可以使用RECURSIVE CTE生成計(jì)數(shù)表:
SET @@cte_max_recursion_depth = 5000;
WITH RECURSIVE cte AS
(
SELECT 1 AS i
UNION ALL
SELECT i+1
FROM cte
WHERE i < 3000
)
SELECT *
FROM cte;
添加回答
舉報(bào)
0/150
提交
取消