我只是想知道如何进行循环并用假数据填充数据库表，以便获得500,000条记录。我有一个包含以下字段的表，对于customer_id，我们有1-1000，staff_id，我们有1-5个工作人员，car_id在1-10,000之间，数量是1-3，date_ordered是从1975年到2017年，date_returned是从1975年到2017年，对于日期，date_ordered和date_returned之间的差应在2-3天之间。
任何帮助，将不胜感激！
CREATE TABLE car_transaction
(
transaction_id INTEGER NOT NULL,
customer_id INTEGER,
staff_id INTEGER,
car_ID INTEGER,
QTY INTEGER,
date_ordered,
date_returned,
PRIMARY KEY (transaction_id));
最佳答案
仅使用纯MySQL SQL代码是完全可能的。
这是我用过的桌子
CREATE TABLE car_transaction
(
transaction_id INTEGER NOT NULL AUTO_INCREMENT, # included AUTO_INCREMENT HERE
customer_id INTEGER,
staff_id INTEGER,
car_ID INTEGER,
QTY INTEGER,
date_ordered DATE, # made DATE type
date_returned DATE, # made DATE type
PRIMARY KEY (transaction_id)
）;
对于customer_id，我们有1-1000，staff_id，我们有1-5个工作人员，car_id是
在1-10,000之间，数量是1-3
这些字段对范围的使用有明确的要求，可以结合使用MySQL rand（）函数和公式来生成这些范围，
SELECT ROUND((RAND() * (MAX - MIN)) + MIN)
例如，对于客户ID，公式为
SELECT ROUND((RAND() * (1000 - 1)) + 1)
第一次尝试结果
ROUND((RAND() * (1000 - 1)) + 1)
----------------------------------
648
第二次尝试结果
ROUND((RAND() * (1000 - 1)) + 1)
----------------------------------
486
date_ordered为1975年至2017年，date_returned为1975年至2017年，
对于日期，date_ordered和date_returned之间的差异
应该在2-3天之间。
日期公式有点复杂。
但是它仍然使用
ROUND((RAND() * (MAX - MIN)) + MIN)公式SELECT DATE(FROM_UNIXTIME(ROUND((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01'))))
第一次尝试结果
DATE(FROM_UNIXTIME(ROUND((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01'))))
-------------------------------------------------------------------------------------------------------------------------------------
2005-08-04
第二次尝试结果
DATE(FROM_UNIXTIME(ROUND((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01'))))
-------------------------------------------------------------------------------------------------------------------------------------
1998-07-22
现在，我们将根据数据生成一个记录，以结合所有最后的步骤。
询问
SELECT
record.customer_id
, record.staff_id
, record.car_id
, record.qty
, record.date_ordered
, record.date_ordered + INTERVAL record.random_day DAY AS date_returned
FROM (
SELECT
(SELECT ROUND((RAND() * (1000 - 1)) + 1)) AS customer_id
, (SELECT ROUND((RAND() * (5 - 1)) + 1)) AS staff_id
, (SELECT ROUND((RAND() * (10000 - 1)) + 1)) AS car_id
, (SELECT ROUND((RAND() * (3 - 1)) + 1)) AS qty
, (DATE(FROM_UNIXTIME(FLOOR((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01')))) ) AS date_ordered
, (SELECT ROUND((RAND() * (3 - 2)) + 2)) AS random_day
FROM
DUAL
)
record
第一次尝试结果
customer_id staff_id car_id qty date_ordered date_returned
----------- -------- ------ ------ ------------ ---------------
633 2 5553 3 2011-11-21 2011-11-24
第二次尝试结果
customer_id staff_id car_id qty date_ordered date_returned
----------- -------- ------ ------ ------------ ---------------
300 4 2380 2 2010-08-21 2010-08-23
程序
DELIMITER $$
CREATE
PROCEDURE generate_random_data_car_transaction(IN numberOfRows INT)
BEGIN
DECLARE counter INT;
SET counter = 1;
WHILE (counter <= numberOfRows) DO
INSERT INTO
car_transaction
(
customer_id
, staff_id
, car_id
, qty
, date_ordered
, date_returned
)
SELECT
record.customer_id
, record.staff_id
, record.car_id
, record.qty
, record.date_ordered
, record.date_ordered + INTERVAL record.random_day DAY AS date_returned
FROM (
SELECT
(SELECT ROUND((RAND() * (1000 - 1)) + 1)) AS customer_id
, (SELECT ROUND((RAND() * (5 - 1)) + 1)) AS staff_id
, (SELECT ROUND((RAND() * (10000 - 1)) + 1)) AS car_id
, (SELECT ROUND((RAND() * (3 - 1)) + 1)) AS qty
, (DATE(FROM_UNIXTIME(FLOOR((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01')))) ) AS date_ordered
, (SELECT ROUND((RAND() * (3 - 2)) + 2)) AS random_day
FROM
DUAL
)
record;
SET counter = counter + 1;
END WHILE;
END$$
DELIMITER ;
呼叫程序
CALL generate_random_data_car_transaction(500000);
询问
SELECT * FROM car_transaction
结果
transaction_id customer_id staff_id car_ID QTY date_ordered date_returned
-------------- ----------- -------- ------ ------ ------------ ---------------
1 757 2 2621 2 1982-03-10 1982-03-13
2 818 1 368 3 1989-06-06 1989-06-08
3 47 2 8538 2 2009-09-30 2009-10-02
4 670 2 4597 2 2005-03-20 2005-03-22
5 216 2 7651 3 2000-10-08 2000-10-10
6 502 2 1364 2 1978-03-28 1978-03-30
7 204 2 1910 2 2009-03-17 2009-03-20
8 398 2 3934 1 2013-07-02 2013-07-04
9 474 1 9286 2 1991-08-06 1991-08-09
10 976 1 724 2 2000-05-09 2000-05-12
...
...
...
499990 20 5 6595 2 1990-05-01 1990-05-03
499991 839 1 7315 2 1989-12-05 1989-12-07
499992 14 3 1274 2 1987-11-12 1987-11-14
499993 539 2 5422 1 1994-06-24 1994-06-26
499994 728 5 7441 3 2000-05-12 2000-05-15
499995 512 3 4039 2 1978-02-03 1978-02-06
499996 732 5 2599 2 1990-01-11 1990-01-14
499997 304 5 6098 2 2011-11-25 2011-11-27
499998 818 2 8196 2 1984-01-14 1984-01-16
499999 617 5 8160 2 2016-03-15 2016-03-18
500000 864 3 7837 2 1980-01-13 1980-01-15
关于mysql - 如何在MySQL中执行循环以填充数据库表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46650817/