建立一个多数据,多字段的大表,索引有意不全,用于多线程测试
1.建表
/*
Navicat Premium Data Transfer
Source Server : duo
Source Server Type : MySQL
Source Server Version : 80300 (8.3.0)
Source Host : localhost:3306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 80300 (8.3.0)
File Encoding : 65001
Date: 31/03/2024 23:49:14
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for test_data_p_bak
-- ----------------------------
DROP TABLE IF EXISTS `test_data_p_bak`;
CREATE TABLE `test_data_p_bak` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL,
`age` int DEFAULT NULL,
`email` varchar(64) DEFAULT NULL,
`area` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`p1` int DEFAULT NULL,
`p2` int DEFAULT NULL,
`p3` int DEFAULT NULL,
`p4` int DEFAULT NULL,
`p5` int DEFAULT NULL,
`p6` int DEFAULT NULL,
`p7` int DEFAULT NULL,
`p8` int DEFAULT NULL,
`p9` int DEFAULT NULL,
`p10` int DEFAULT NULL,
`p11` int DEFAULT NULL,
`p12` int DEFAULT NULL,
`p13` int DEFAULT NULL,
`p14` int DEFAULT NULL,
`p15` int DEFAULT NULL,
`p16` int DEFAULT NULL,
`p17` int DEFAULT NULL,
`p18` int DEFAULT NULL,
`p19` int DEFAULT NULL,
`p20` int DEFAULT NULL,
`p21` int DEFAULT NULL,
`p22` int DEFAULT NULL,
`p23` int DEFAULT NULL,
`p24` int DEFAULT NULL,
`p25` int DEFAULT NULL,
`p26` int DEFAULT NULL,
`p27` int DEFAULT NULL,
`p28` int DEFAULT NULL,
`p29` int DEFAULT NULL,
`p30` int DEFAULT NULL,
`p31` int DEFAULT NULL,
`p32` int DEFAULT NULL,
`p33` int DEFAULT NULL,
`p34` int DEFAULT NULL,
`p35` int DEFAULT NULL,
`p36` int DEFAULT NULL,
`p37` int DEFAULT NULL,
`p38` int DEFAULT NULL,
`p39` int DEFAULT NULL,
`p40` int DEFAULT NULL,
`p41` int DEFAULT NULL,
`p42` int DEFAULT NULL,
`p43` int DEFAULT NULL,
`p44` int DEFAULT NULL,
`p45` int DEFAULT NULL,
`p46` int DEFAULT NULL,
`p47` int DEFAULT NULL,
`p48` int DEFAULT NULL,
`p49` int DEFAULT NULL,
`p50` int DEFAULT NULL,
`p51` int DEFAULT NULL,
`p52` int DEFAULT NULL,
`p53` int DEFAULT NULL,
`p54` int DEFAULT NULL,
`p55` int DEFAULT NULL,
`p56` int DEFAULT NULL,
`p57` int DEFAULT NULL,
`p58` int DEFAULT NULL,
`p59` int DEFAULT NULL,
`p60` int DEFAULT NULL,
`p61` int DEFAULT NULL,
`p62` int DEFAULT NULL,
`p63` int DEFAULT NULL,
`p64` int DEFAULT NULL,
`p65` int DEFAULT NULL,
`p66` int DEFAULT NULL,
`p67` int DEFAULT NULL,
`p68` int DEFAULT NULL,
`p69` int DEFAULT NULL,
`p70` int DEFAULT NULL,
`p71` int DEFAULT NULL,
`p72` int DEFAULT NULL,
`p73` int DEFAULT NULL,
`p74` int DEFAULT NULL,
`p75` int DEFAULT NULL,
`p76` int DEFAULT NULL,
`p77` int DEFAULT NULL,
`p78` int DEFAULT NULL,
`p79` int DEFAULT NULL,
`p80` int DEFAULT NULL,
`p81` int DEFAULT NULL,
`p82` int DEFAULT NULL,
`p83` int DEFAULT NULL,
`p84` int DEFAULT NULL,
`p85` int DEFAULT NULL,
`p86` int DEFAULT NULL,
`p87` int DEFAULT NULL,
`p88` int DEFAULT NULL,
`p89` int DEFAULT NULL,
`p90` int DEFAULT NULL,
`p91` int DEFAULT NULL,
`p92` int DEFAULT NULL,
`p93` int DEFAULT NULL,
`p94` int DEFAULT NULL,
`p95` int DEFAULT NULL,
`p96` int DEFAULT NULL,
`p97` int DEFAULT NULL,
`p98` int DEFAULT NULL,
`p99` int DEFAULT NULL,
`p100` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1100002 DEFAULT CHARSET=utf8mb3;
SET FOREIGN_KEY_CHECKS = 1;
2.建立存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_insert_p`(IN num INT,IN area varchar(10))
BEGIN
DECLARE n INT DEFAULT 1;
WHILE n <= num DO
INSERT INTO test_data_p_bak(name,age,email,area,
p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p9,
p10,
p11,
p12,
p13,
p14,
p15,
p16,
p17,
p18,
p19,
p20,
p21,
p22,
p23,
p24,
p25,
p26,
p27,
p28,
p29,
p30,
p31,
p32,
p33,
p34,
p35,
p36,
p37,
p38,
p39,
p40,
p41,
p42,
p43,
p44,
p45,
p46,
p47,
p48,
p49,
p50,
p51,
p52,
p53,
p54,
p55,
p56,
p57,
p58,
p59,
p60,
p61,
p62,
p63,
p64,
p65,
p66,
p67,
p68,
p69,
p70,
p71,
p72,
p73,
p74,
p75,
p76,
p77,
p78,
p79,
p80,
p81,
p82,
p83,
p84,
p85,
p86,
p87,
p88,
p89,
p90,
p91,
p92,
p93,
p94,
p95,
p96,
p97,
p98,
p99,
p100
)values(concat('alice',n),rand()*50,concat('alice',n,'@qq.com'),
area,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100,
FLOOR((11) * RAND()) + 100
);
set n=n+1;
end while;
ENd
3.插入数据
每次更改area字段后进行插入,方便后期多线程测试时间。
call Proc_insert_p(100000,'32401')