mysql - 在MYSQL中的表中插入0到999999行

Question

我在 mysql 中编写了以下简单程序来运行插入语句 1,000,000 次。

 DELIMITER $$
 DROP PROCEDURE IF EXISTS INIT_DEGREE_PRECISION$$
 CREATE PROCEDURE INIT_DEGREE_PRECISION()
       BEGIN
               DECLARE x  INT;
               DECLARE zeros  VARCHAR(8);
               DELETE FROM degree_precision;
               SET x = 1;
               WHILE x  <= 999999 DO
                           insert into degree_precision (degree_precision_id) values (x);
                           SET  x = x + 1;
               END WHILE;
       END$$
 DELIMITER ;

但是当我调用这个过程时，它在我的本地机器上花费了太多时间。我应该在远程服务器上运行它。有更好的方法吗？

我想做什么？

我有一个表，在表 degree_precision 中只包含一列 degree_precision_id。该表将只有 1,000,000 行具有 degree_precision_id 值 0 - 999999。为此，我编写了一个程序，但这需要大量时间。

Answer 1

我建议使用交叉连接:

insert into degree_precision (degree_precision_id)
    select (@rn := @rn + 1) - 1 as rn
    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) d1 cross join 
         (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) d2 cross join 
         (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) d3 cross join 
         (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) d4 cross join 
         (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) d5 cross join 
         (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) d6 cross join
         (select @rn := 0) params

请注意，create table as 可能更快，因为有日志记录开销。