建立mysql测试数据

建立一个多数据,多字段的大表,索引有意不全,用于多线程测试

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')

相关推荐

  1. 建立mysql测试数据

    2024-04-03 18:48:02       6 阅读
  2. sqlite建立数据库

    2024-04-03 18:48:02       4 阅读

最近更新

  1. leetcode705-Design HashSet

    2024-04-03 18:48:02       5 阅读
  2. Unity发布webgl之后打开streamingAssets中的html文件

    2024-04-03 18:48:02       5 阅读
  3. vue3、vue2中nextTick源码解析

    2024-04-03 18:48:02       6 阅读
  4. 高级IO——React服务器简单实现

    2024-04-03 18:48:02       5 阅读
  5. 将图片数据转换为张量(Go并发处理)

    2024-04-03 18:48:02       4 阅读
  6. go第三方库go.uber.org介绍

    2024-04-03 18:48:02       6 阅读
  7. 前后端AES对称加密 前端TS 后端Go

    2024-04-03 18:48:02       7 阅读

热门阅读

  1. postcss使用简明教程

    2024-04-03 18:48:02       5 阅读
  2. 泰坦尼克号幸存者预测

    2024-04-03 18:48:02       6 阅读
  3. 【Python 笔记1】字典

    2024-04-03 18:48:02       3 阅读
  4. Power Automate里的常用方法

    2024-04-03 18:48:02       5 阅读
  5. Kingbase简单存储过程

    2024-04-03 18:48:02       5 阅读
  6. 设计模式 - Provider 模式

    2024-04-03 18:48:02       5 阅读
  7. dotnet依赖注入与IOC(包含Autofac的使用)

    2024-04-03 18:48:02       4 阅读