创建“数字表"在 MySQL 中

2021-11-20 00:00:00 sql mysql

我正在尝试在 MySQL 中生成一个大的连续数字表.

I'm trying to generate a big table of consecutive numbers in MySQL.

我只想要 2 列；一个主键和一个范围为 0-X 的数字列，其中 X 非常大.大约64,000 行应该这样做.我试过这段代码没有成功:

I just want 2 columns; a primary key and a numbers column with a range of 0-X, where X is very large. Approx. 64,000 rows should do it. I've tried this code with no success:

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 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 3 行的CONSTRAINT XPKnumbers PRIMARY KEY CLUSTERED (number) ) INSERT INTO n"附近使用的正确语法

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT XPKnumbers PRIMARY KEY CLUSTERED (number) ) INSERT INTO n' at line 3

有人对这项工作有什么建议吗?

Anybody have any suggestions to make this work?

推荐答案

您缺少分号、逗号，即使在更正语法之后，每次从表中选择最大值只是为了再插入一行仍然不是一个好主意循环中.

You are missing semicolons, commas, and even after correcting syntax it is still not a good idea to select max from the table every time just to insert one more row in a loop.

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

Drop that and use generators from 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;

如果出于某种原因您确实需要一个数字表，请执行以下操作:

And if for whatever reason you really need a table of numbers just do:

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

相关文章