在MySQL中创建“Numbers Table”

一尘不染

在MySQL中创建“Numbers Table”

mysql

我正在尝试在MySQL中生成一个大的连续Numbers Table。

我只想要2列；一个主键和一个数字列，范围在0-X之间，其中X很大。大约应该有64,000行。我尝试了以下代码，但没有成功：

CREATE TABLE numbers (
   number           INT         NOT NULL
   CONSTRAINT XPKnumbers
      PRIMARY KEY CLUSTERED (number)
    )

INSERT INTO numbers (number) VALUES (0)

DECLARE @i          INT
SET @i = 20

WHILE 0 < @i
   BEGIN
      INSERT INTO numbers (number)
         SELECT number + (SELECT 1 + Max(number) FROM numbers)
            FROM numbers

      SET @i = @i - 1
   END

SELECT * FROM numbers

我得到这个错误：

＃1064-您的SQL语法有误；在第3行的“ CONSTRAINT XPKnumbers PRIMARY KEY
CLUSTERED（number））INSERT INTO n”附近检查与您的MySQL服务器版本相对应的手册以使用正确的语法。

有人对这项工作有任何建议吗？

阅读 488

2020-05-17

共1个答案

一尘不染

您会丢失分号，逗号，即使在更正语法之后，每次仅在循环中再插入一行也不是一个好主意。

从 http://use-the-index-luke.com/blog/2011-07-30/mysql-row-
generator删除并使用生成器：

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 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   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

如果出于任何原因您确实需要数字表，请执行以下操作：

INSERT INTO numbers(number)
SELECT n FROM generator_64k WHERE n < 64000

2020-05-17