使用循环在mysql中插入100000数据 [英] using loop to insert 100000 data in mysql
本文介绍了使用循环在mysql中插入100000数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在mysql中使用循环插入data(100000).
当我运行时没有错误,但是数据没有插入到我的表中.
I want to insert data(100000) with a loop in mysql.
When i run no error,but data not insert into my table.
这是我的查询:
DELIMITER //
DECLARE i INT DEFAULT 1;
WHILE i <= 100 DO
INSERT INTO vouchers VALUES (i,'val1','val2','val3');
SET i = i + 1;
END WHILE;
DELIMITER ;
结构表:
id int(11)[autoincrement]
data1 varchar(100)
data2 varchar(100)
data3 varchar(100)
如何解决我的查询? 谢谢.
how to fix my query ? thanks..
推荐答案
您可以使用以下笨拙的语句插入100000行,而无需执行任何过程:
You could insert your 100000 rows without a procedure with this unwieldy statement:
INSERT INTO
vouchers
SELECT
t.n, 'val1', 'val2', 'val3'
FROM (
SELECT
1 + a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 AS n
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) a
,(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) b
,(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) c
,(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) d
,(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) e
ORDER BY n
) t;
要使用自动递增功能,请将t.n替换为NULL:
To use the autoincrement feature replace the t.n by NULL:
INSERT INTO
vouchers
SELECT
NULL, 'val1', 'val2', 'val3'
FROM (
SELECT
1 + a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 AS n
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) a
,(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) b
,(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) c
,(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) d
,(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) e
ORDER BY n
) t;
这篇关于使用循环在mysql中插入100000数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文