MySQL - 如何快速插入百万行？ [复制]

因此，出于开发目的，我需要一个包含大约 100 万到 1 亿个值的表，我当前的方法一点也不快。

开发人员通常会做什么来让他们的表拥有数百万行来测试他们的程序处理这些数据的速度？

我当前的方法有 10 个 for 循环，但对于我需要的行数来说，它真的很慢。

那么我该怎么做才能快速插入数百万行？在这种情况下，专业开发人员会做什么？

从您的问题中不清楚您需要插入的数据的性质是什么，但如果它可以动态生成，那么最快的方法就是在一个查询中执行（这将插入 100 万客户），如下所示

INSERT INTO customers (id, customer_name)
SELECT n, CONCAT('Customer', n)
  FROM
(
select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + 1 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
      , (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) f
) t

Here is SQL小提琴 http://sqlfiddle.com/#!2/e14238/210k 行演示

推荐阅读：

• MySQL：有效地填充存储过程中的表 https://stackoverflow.com/a/17139749/1920232
• INSERT 语句的速度 http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html