在MYSQL的表格中插入0至999999行 [英] Insert 0 to 999999 rows in a table in MYSQL

问题描述

我已经在mysql中编写了以下简单过程来运行插入语句1,000,000次.

I have written following simple procedure in mysql to run insert statement 1,000,000 times.

 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 ;

但是，当我调用此过程时，它在我的本地计算机上花费了太多时间.我应该在远程服务器上运行它.有什么更好的方法吗?

But when I am calling this procedure it is taking too much time on my local machine. I am supposed to run this on remote server. Is there any better way of doing this?

我要做什么?

我有一个表，该表仅包含表degree_precision中的一列degree_precision_id.该表将只有1,000,000个行，其行的degree_precision_id值为0 - 999999.为此，我编写了一个过程，但是要花很多时间.

I have a table that contain only one column degree_precision_id in table degree_precision. This table will have only 1,000,000 no of rows with degree_precision_id values 0 - 999999. To do this I have written a procedure but it takes hell lot of time.

推荐答案

我建议使用cross join:

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可能更快.

Note that create table as is probably faster, because of logging overhead.

这篇关于在MYSQL的表格中插入0至999999行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！