简介
场景1:测试sql服务器性能时需要单表100万以上数据时
场景2:业务测试数据1000个账号每个账号有5个商品 当我们遇到以上场景时,如何快速造数据?
原理
利用select的交叉连接(cross join)。如果不带WHERE条件子句,它将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积;
需要预先准备好表和样本数据
造出10万,100万,1000万个用户?
创建一张用户表
CREATE TABLE account(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
如何造出待插入造出10万个用户数据
从account表中看出,我们需要制造出10万条以上不同的name
name为NAME1,NAME2,NAME3,...,NAME100000
创建一个有10条数据的表
CREATE TABLE sample(
id INT(11) NOT NULL AUTO_INCREMENT,
value INT(5) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
INSERT INTO sample(value)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
cross join测试
-- 显示10条数据,列的偏移值为10000
SELECT
@rownum := @rownum + 1
FROM
sample,
(SELECT @rownum := 10000) AS v;
-- 显示10×10条数据,列的偏移值为10000
SELECT
@rownum := @rownum + 1
FROM
sample AS s1,
sample AS s2,
(SELECT @rownum := 10000) AS v;
插入10万条用户数据,即10×10×10×10×10
INSERT INTO account(name)
SELECT
CONCAT('NAME' , @rownum := @rownum + 1)
FROM
sample AS s1,
sample AS s2,
sample AS s3,
sample AS s4,
sample AS s5,
(SELECT @rownum := 0) AS v
执行结果 0.799 sec 是不是很激动?
INSERT INTO account(name)
SELECT
CONCAT('NAME' ,
@rownum := @rownum + 1) FROM
sample AS s1,
sample AS s2,
sample AS s3,
sample AS s4,
sample AS s5,
(SELECT @rownum := 0) AS v
100000 row(s) affected Records: 100000 Duplicates: 0 Warnings: 0 0.799 sec
100个账号每个账号有10个商品
创建一个商品表
CREATE TABLE item(
id INT(11) NOT NULL AUTO_INCREMENT,
account_id INT(11) NOT NULL,
master_item_id INT(11) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
商品表样本数据
INSERT INTO item(account_id, master_item_id)
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5);
1000个账号每个账号有5个商品。新增为(账号为2~1000,账号1为样本数据)
INSERT INTO item(account_id, master_item_id)
SELECT
a.id,
i.master_item_id
FROM
item AS i,
account AS a
WHERE
i.account_id = 1
AND
a.id BETWEEN 2 AND 1000
执行结果 0.125 sec
INSERT INTO item(account_id, master_item_id)
SELECT a.id, i.master_item_id FROM item AS i, account AS a
WHERE
i.account_id = 1 AND a.id BETWEEN 2 AND 1000
4995 row(s) affected Records: 4995 Duplicates: 0 Warnings: 0 0.125 sec
---------------------
作者:huuinn
来源:CSDN
版权声明:本文为博主原创文章,转载请附上博文链接!