MySQL 用大量测试数据填充数据库表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3766282/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fill database tables with a large amount of test data
提问by Mohammad Umair
I need to load a table with a large amount of test data. This is to be used for testing performance and scaling.
我需要加载一个包含大量测试数据的表。这将用于测试性能和扩展。
How can I easily create 100,000 rows of random/junk data for my database table?
如何为我的数据库表轻松创建 100,000 行随机/垃圾数据？
回答by Daniel Vassallo
You could also use a stored procedure. Consider the following table as an example:
您还可以使用存储过程。以下表为例：
CREATE TABLE your_table (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, val int);
Then you could add a stored procedure like this:
然后你可以添加一个这样的存储过程：
DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
DECLARE i INT DEFAULT 100;
WHILE i < 100000 DO
INSERT INTO your_table (val) VALUES (i);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
When you call it, you'll have 100k records:
当您调用它时，您将拥有 100k 条记录：
CALL prepare_data();
回答by michalzuber
For multiple row cloning (data duplication) you could use
对于多行克隆（数据复制），您可以使用
DELIMITER $$
CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 100000 DO
INSERT INTO `table` (`user_id`, `page_id`, `name`, `description`, `created`)
SELECT `user_id`, `page_id`, `name`, `description`, `created`
FROM `table`
WHERE id = 1;
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL insert_test_data();
DROP PROCEDURE insert_test_data;
回答by Daneel S. Yaitskov
Here it's solution with pure math and sql:
这是纯数学和 sql 的解决方案：
create table t1(x int primary key auto_increment);
insert into t1 () values (),(),();
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 1265 rows affected (0.01 sec)
Records: 1265 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 2530 rows affected (0.02 sec)
Records: 2530 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 5060 rows affected (0.03 sec)
Records: 5060 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 10120 rows affected (0.05 sec)
Records: 10120 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 20240 rows affected (0.12 sec)
Records: 20240 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 40480 rows affected (0.17 sec)
Records: 40480 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 80960 rows affected (0.31 sec)
Records: 80960 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 161920 rows affected (0.57 sec)
Records: 161920 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 323840 rows affected (1.13 sec)
Records: 323840 Duplicates: 0 Warnings: 0
mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 647680 rows affected (2.33 sec)
Records: 647680 Duplicates: 0 Warnings: 0
回答by gmarcotte
If you want more control over the data, try something like this (in PHP):
如果您想更好地控制数据，请尝试以下操作（在 PHP 中）：
<?php
$conn = mysql_connect(...);
$num = 100000;
$sql = 'INSERT INTO `table` (`col1`, `col2`, ...) VALUES ';
for ($i = 0; $i < $num; $i++) {
mysql_query($sql . generate_test_values($i));
}
?>
where function generate_test_values would return a string formatted like "('val1', 'val2', ...)". If this takes a long time, you can batch them so you're not making so many db calls, e.g.:
其中函数 generate_test_values 将返回一个格式为“('val1', 'val2', ...)”的字符串。如果这需要很长时间，您可以批量处理它们，这样您就不会进行太多的数据库调用，例如：
for ($i = 0; $i < $num; $i += 10) {
$values = array();
for ($j = 0; $j < 10; $j++) {
$values[] = generate_test_data($i + $j);
}
mysql_query($sql . join(", ", $values));
}
would only run 10000 queries, each adding 10 rows.
只会运行 10000 个查询，每个查询添加 10 行。
回答by ganesh konathala
回答by Max
create table mydata as select * from information_schema.columns;
insert into mydata select * from mydata;
-- repeating the insert 11 times will give you at least 6 mln rows in the table.
