问题描述
我已经在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行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!