醉里挑灯看剑

0%

MySQL 使用存储过程模拟数据批量插入

发表于 分类于 ，

有时我们需要进行 MySQL 大量数据的查询、性能、索引测试，通过程序（多线程）灌入数据非常慢，可以通过存储过程批量插入模拟数据。

以下我们创建了一个类似文章内容的表结构，改表结构含有各种类型的字段，通过存储过程对各种字段进行有条件的随机模拟插入。

创建 content 表

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `content` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `md5` varchar(32) NOT NULL DEFAULT '',
  `title` varchar(200) NOT NULL DEFAULT '',
  `catid` int(10) unsigned NOT NULL DEFAULT '0',
  `modelid` tinyint(3) NOT NULL DEFAULT '0',
  `published` int(10) DEFAULT NULL,
  `publishedby` int(10) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建随机字符串函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$
DROP FUNCTION IF EXISTS `rand_string`$$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN
          DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
          DECLARE return_str VARCHAR(255) DEFAULT '';
          DECLARE i INT DEFAULT 0;
          WHILE i<n DO
              SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
              SET i = i+1;
          END WHILE;
    RETURN return_str;
END$$
DELIMITER ;

创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
DELIMITER $$
DROP PROCEDURE IF EXISTS `insert_content` $$
CREATE PROCEDURE `insert_content`(START INT(10), MAX INT(10))
BEGIN
     DECLARE i INT DEFAULT 0;
     DECLARE catid INT;
     DECLARE modelid INT;
     DECLARE published INT;
     DECLARE publishedby INT;
     DECLARE STATUS INT;
     DECLARE times INT;
     DECLARE DATETIME INT;
     SET autocommit = 0;
     REPEAT
         SET i = i+1;
         SET STATUS = 6;
         SET catid = FLOOR(1+RAND()*10000);
         SET modelid = 1;
         SET published = FLOOR(1000000000+RAND()*451577599);
         SET publishedby = FLOOR(1+RAND()*300);
         SET times = FLOOR(1000000000+RAND()*451577599);
         SET DATETIME = FLOOR(1000000000+RAND()*451577599);
         IF (i % 50 = 0) THEN
          SET STATUS= FLOOR(1+RAND()*5);
          SET published = NULL;
          SET publishedby = NULL;
          SET times = NULL;
          SET DATETIME = NULL;
         END IF;
         IF (i % 4 = 0) THEN
          SET modelid = FLOOR(2+RAND()*9);
         END IF;
         INSERT INTO content VALUES((START+i), MD5(START+i), rand_string(FLOOR(20+RAND()*180)), catid, modelid, published, publishedby, FROM_UNIXTIME(times), FROM_UNIXTIME(DATETIME), STATUS);
         UNTIL i = MAX
     END REPEAT;
     COMMIT;
END $$
DELIMITER ;

使用存储过程和测试

1
2
3
CALL insert_content(10000, 10000);

SELECT COUNT(*) FROM content;